Update vs Alter
Update and Alter are two SQL (Structured Query Language) commands used for modifying databases. Update statement is used to update existing records in a database. Update is a Data Manipulation Language (DML) statement. Alter SQL command is used to modify, delete or add a column to an existing table in a database. Alter is a Data Definition Language (DDL) statement.
What is Update?
Update is a SQL command that is used to update existing records in a database. Update is considered as a DML statement. Commands that are used to manage data without altering the data base schema are called DML statements. Following is the typical syntax of an update statement.
UPDATE tableName
SET column1Name=value1, column2Name=value2, …
WHERE columnXName=someValue
In the above example tableName should be replaced with the name of the table you want to modify the records in. The column1Name, column2Name in the SET clause are the names of the columns in the table in which the values of the record that needs to be modified. value1 and value2 are the new values that should be inserted in the record. WHERE clause specifies the set of records needs to be updated in the table. WEHRE clause could also be omitted from the UPDATE statement. Then all the records in the table would be updated with the values provided in the SET clause.
What is Alter?
Alter is a SQL command that is used to modify, delete or add a column to an existing table in a database. Alter is considered as a DDL statement. Commands that are used to define the structure of a database (database schema) are called DDL statements. Following is the typical syntax of an alter statement that is used to add a column to an existing table.
ALTER TABLE tableName
ADD newColumnName dataTypeOfNewColumn
In here tableName is the name of the existing table that needs to be altered and newColumnName is the name given to the new column that is added to the table. dataTypeOfNewColumn provides the data type of the new column.
Following is the typical syntax of an alter statement that is used to delete a column in an existing table.
ALTER TABLE tableName
DROP COLUMN columnName
In here, tableName is the name of the existing table that needs to be altered and the columnName is the name of the column that needs to be deleted. Some of the tables might not allow deleting columns from its tables.
Following is the typical syntax of an alter statement that is used to change the data type of an existing column in a table.
ALTER TABLE tableName
ALTER COLUMN columnName newDataType
In here columnName is the name of the existing column in the table and the newDataType is the name of the new data type.
What is the difference between Update and Alter?
Update is a SQL command that is used to update existing records in a database, while alter is a SQL command that is used to modify, delete or add a column to an existing table in a database.
Update is a DML statement whereas alter is a DDL statement. Alter command modifies the database schema, while update statement only modifies records in a database without modifying its structure.