Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between Insert and Update and Alter

Difference Between Insert and Update and Alter

July 5, 2011 Posted by Indika

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.

Related posts:

Difference Between Update and Alter Difference Between MySQL and MS SQL Server Difference Between DBMS and Database Difference Between Data Mining and Query Tools Difference Between SAP and ORACLE

Filed Under: Database Tagged With: Alter, DDL statement, DML Statements, Insert, SQL, SQL Commands, Update

About the Author: Indika

Indika, BSc.Eng, MSECE Computer Engineering, PhD. Computer Science, is an Assistant Professor and has research interests in the areas of Bioinformatics, Computational Biology, and Biomedical Natural Language Processing.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between Metals and Nonmetals

Difference Between Metals and Nonmetals

Difference Between Rigor Mortis and Cadaveric Spasm

Difference Between Rigor Mortis and Cadaveric Spasm

What is the Difference Between Carbonyl Iron and Ferrous Ascorbate

What is the Difference Between Carbonyl Iron and Ferrous Ascorbate

Difference Between Management Accounting and Cost Accounting

Difference Between Internal and External Business Environment

Difference Between Internal and External Business Environment

Latest Posts

  • What is the Difference Between Chlorine Fluorine and Astatine
  • What is the Difference Between B Cell and T Cell Leukemia
  • What is the Difference Between Animal Pole and Vegetal Pole
  • What is the Difference Between Terminal and Bridging Carbonyls
  • What is the Difference Between Mycobacterium Tuberculosis and Mycobacterium Leprae
  • What is the Difference Between Cyanuric Acid and Muriatic Acid
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.