Difference Between ALTER and UPDATE Command in SQL (with Comparison Chart)
Table of Contents
ALTER and UPDATE are the two modifying commands of SQL. ALTER is used to modify the structure of the relations (Tables) in the database. UPDATE Command is used to modify the data stored in a relation of the database. The basic difference between ALTER and UPDATE Command is that ALTER command is a Data Definition Language command whereas the UPDATE command is a Data Manipulation Language command.
Let us discuss the differences between ALTER and UPDATE Command with the help of the comparison chart shown below.
Content: ALTER Vs UPDATE Command
Comparison Chart
| Basis for Comparison | ALTER | UPDATE |
|---|---|---|
| Basic | ALTER command is a Data Definition Language Command. | UPDATE command is a Data Manipulation Language Command. |
| Purpose | ALTER Command add, delete, modify the attributes of the relations (tables) in the database. | UPDATE Command modifies one or more records in the relations. |
| Syntax | ALTER TABLE table_name ADD column_name datatype; | UPDATE table_name SET column_name1 = value, column_name2 = value, ... |
| Values | ALTER Command by default initializes values of all the tuple as NULL. | UPDATE Command sets specified values in the command to the tuples. |
| Specification | ALTER Command operates on the attribute of a relation. | Update Command operates on the attribute value of a specific tuple in a relation. |
Definition of ALTER Command
The ALTER command is a Data Definition Language (DDL) Command. This command modifies the structure or definition of a relation that already exist in the database. Modifying the structure of a relation means, you can add columns, delete or drop columns, rename a column’s name, resize columns, or you can change the data type of the columns of a table (relation) that already exists in the database.
So, we can say that ALTER command operates on columns or attributes only, as columns in a relation refer to the attributes of that relation. Whenever ALTER command adds any new column or attribute in a relation then it by default initializes the value of that attribute for all tuple as NULL.
The Syntax of ALTER command is discussed below:
/* add a column to the existing table*/ ALTER TABLE table_name ADD column_name column-definition; /* drop a column from the existing table*/ ALTER TABLE table_name DROP COLUMN column_name; /* rename a column in the existing table*/ ALTER TABLE table_name RENAME COLUMN old_name TO new_name; /* Alter the datatype of an already existing column in the table*/ ALTER TABLE table_name ALTER COLUMN column_name column_type;
The above commands are the generalised form of add, delete, rename commands.
Definition of UPDATE Command
The UPDATE command is a Data Manipulation Language (DML) command. It modifies the attribute values of one or more tuples in a relation. When WHERE clause is used along with the UPDATE command, it helps in selecting the tuples whose attribute values are to be modified.
On the other hands, SET clause when used along with UPDATE specifies the attribute name that are to be modified along with the values that will be assigned to them. It is possible to assign NULL or DEFAULT as a value the attribute.
Let us discuss the syntax of UPDATE command:
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
In the command above, you can see that the WHERE clause specifies the tuple whose attribute value is to be modified. The SET clause specifies attribute name and the value that is to be assigning to the attribute of the specific tuple in a relation.
Key Differences Between ALTER and UPDATE Command in SQL
Conclusion
ALTER Command is used to modify the structure of the relation in the database. UPDATE Command is used to modify the values of the records or tuples in the relation
ncG1vNJzZmislZi1pbXFn5yrnZ6YsrR6wqikaJyZm7OmvsSnmp5lkprBuLHEp2SapKSav26tzZ1krqiUlsGmecKopKaZnpl6qrqMrKilZpipuq0%3D