Difference Between ALTER and UPDATE Command in SQL

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

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

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.

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