Insert vs Update vs Alter
Insert, Update and Alter are three SQL (Structured Query Language) commands used for modifying databases. Insert statement is used for inserting a new row to an existing table. Update statement is used to update existing records in a database. Insert and Update are Data Manipulation Language (DML) statements. 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.
Insert
Insert is a SQL command used to insert a new row to an existing table. Insert is a DML statement. Commands that are used to manage data without altering the database schema are called DML statements. There are two ways that an Insert statement could be written.
One format specifies the names of the columns and the values that need to be inserted as follows.
INSERT INTO tableName (column1Name, column2Name, …)
VALUES (value1, value2, …)
The second format does not specify the column names that the values should be inserted.
INSERT INTO tableName
VALUES (value1, value2, …)
In the above examples, tableName is the name of the table that the rows should be inserted. The column1Name, column2Name, … are the names of the columns that the values value1, value2, … will be inserted.
Update
Update is a SQL command that is used to update existing records in a database. Update is considered as a DML statement. 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 that you want to modify the records. 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. WHERE 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 Inset, Update and Alter?
Insert command is used to insert a new row to an existing table, 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. Insert and Update are DML statement whereas, alter is a DDL statement. Alter command modifies the database schema, while insert and update statements only modifie records in a database or insert records in to a table, without modifying its structure.