Difference Between ALTER and UPDATE Command in SQL
FREE
What is ALTER Command in SQL?
The ALTER command in SQL is a DDL (Data Definition Language) statement which means that it is used for updating the structure of tables in a database. With the ALTER command, we can add, delete, or modify columns in an existing table.
Syntax of the ALTER command is as follows:
- ALTER command for adding a column to the existing table in the database
- ALTER command for dropping a column from the existing table
- ALTER command for renaming a column in the existing table
- ALTER command for modifying the data type of an already existing column in the table
Features of the ALTER command:
- The operations are performed on a structural level instead of a data level.
- It is used to add, delete and modify table attributes in the database.
- By using the 'ALTER' command in the table, the default value of the new column added is initialized to NULL.
What is UPDATE Command in SQL?
SQL's UPDATE command is a DML (Data Manipulation Language) statement. It is used for manipulating the data of existing columns but can’t change the definition of a table.
Syntax of the UPDATE command is as follows:
Note: Without using the WHERE clause, all records of the table will get updated.
Features of UPDATE command:
- Operations are performed on the data level by using this command.
- Existing records get updated if this command is used.
- It is used to set specified values in the tuple.
- It is used for changing data inside the table.
Examples of ALTER and UPDATE Command in SQL
Table name: Movies
| Moviename | Moviedate |
|---|---|
| Spiderman | 2020 |
| Endgame | 2021 |
Example of ALTER Command:
Output:
| Moviename | Moviedate | Movierate |
|---|---|---|
| Spiderman | 2020 | |
| Endgame | 2021 |
Explanation: In the given example, the table named Movies will be altered, and the new column Movierate will be added with the data type of int and length of the value of that column as 5.
Example of UPDATE Command:
Output:
| Moviename | Moviedate | Movierate |
|---|---|---|
| Spiderman | 2020 | 4 |
| Endgame | 2021 |
Explanation: In this example, the value of the column named Movierate will be set to 4 for the row where the Moviename value is 'Spiderman'.
Difference Between ALTER and UPDATE Command in SQL?
| ALTER Command | UPDATE Command |
|---|---|
| It is a DDL language (Data Definition Language) | It is a DML language (Data Manipulation Language) |
| ALTER command will perform all the actions in the table at a structural level. | UPDATE command will perform all the actions in the table at the data level. |
| It is used for adding, deleting, and modifying attributes of the table in the database. | It is used for updating the data (records) in the existing table. |
| By default, all the values in the tuple are initialized as NULL if the ALTER command is used. | It sets the specified value to the tuple if this command is used. |
| Changes are made to the table structure. | Changes are made to the data inside the table. |
| Usage: Structure of a table, name of table, functions, etc. | Usage: change data of row or column. |
| Example: ALTER TABLE Movies ADD COLUMN Movierate int(5); | Example: UPDATE Movies SET Movierate = 4 WHERE Moviename='Spiderman'; |
Conclusion
I hope you like this article. Here are a few key takeaways from this article:
- ALTER command in SQL is a DDL (Data Definition Language) statement. It is mainly used for updating the structure of tables in a database by using keywords like add, delete and modify attributes of tables.
- UPDATE command in SQL is a DML (Data Manipulation Language) statement. It is mainly used for manipulating the data of existing columns but can’t change the definition of a table.
- The main difference between the two is that the ALTER command adds, deletes, modifies, renames the attributes of the relation, and the UPDATE command modifies the values of the records in the relations.
- ALTER command is attribute or column specific, and the UPDATE command is attribute-value-specific.