What is the Difference Between INSERT and UPDATE in SQL

The main difference between INSERT and UPDATE in SQL is that INSERT is used to add new records to the table while UPDATE is used to modify the existing records in the table.  

Relational Database Management System (RDBMS) is a program that allows storing and managing relational databases. A relational database stores data in tables. These tables are linked to each other. Structured Query Language (SQL) is the language to perform various operations on the data stored in RDBMS. One SQL command type is called Data Manipulation Language (DML). These commands help to manipulate the data stored in the tables. INSERT and UPDATE are two DML commands. Overall, INSERT helps to add one or more rows to a table whereas UPDATE helps to change one or more values in a table.

Key Areas Covered

1. What is INSERT
     – Definition, Functionality
2. What is UPDATE
     – Definition, Functionality
3. What is the Difference Between INSERT and UPDATE in SQL
     – Comparison of Key Differences

Key Terms

INSERT, DML, Relational Database, SQL, UPDATE

What is INSERT in SQL

INSERT command helps to add new records to a table. The employee table is as follows.

The following statement adds new values to the employee table.

INSERT INTO employee VALUES (3, ‘John’, ‘Melbourne’, ‘Marketing’);

Now the employee table is as follows.

It is also possible to write the above SQL statement as follows.

INSERT INTO employee (emp_id, name, address, department) VALUES (3, ‘John’, ‘Melbourne’, ‘Marketing’);

What is UPDATE in SQL

UPDATE command helps to change the existing records of a table. We can use it with the WHERE clause to change specific record or records.

The following statement changes the department of employee 3 to Sales.

UPDATE employee SET department = ‘Sales’ WHERE emp_id = 3;

Now the table is as follows.

Furthermore, it is also possible to change the values of multiple columns.

UPDATE employee SET address = ‘Canberra’, department = ‘Engineering’ WHERE emp_id=2;

Above statement changes the address and department of the employee 2 as follows.

Difference Between INSERT and UPDATE in SQL

Definition

INSERT is a DML command for inserting one or more rows into a table in an RDBMS whereas UPDATE is a DML command to change or update values in a table of an RDBMS. Thus, this is the main difference between INSERT and UPDATE in SQL.

Usage

An important difference between INSERT and UPDATE in SQL is that while INSERT helps to add new values to the table, UPDATE helps to modify the values of a table.

Conclusion

INSERT and UPDATE are two widely used DML commands in SQL. Usually, INSERT statement is the first statement executed after creating the table. On the other hand, we can use WHERE clause with UPDATE to limit the modification to specific records.  Thus, the difference between INSERT and UPDATE in SQL is that INSERT is used to add new records to the table while UPDATE is used to modify the existing records in the table. 

Reference:

1. “Using INSERT SQL Command.” Types of Network Topology in Computer Networks | Studytonight, Available here.
2. “What Is Insert? – Definition from Techopedia.” Techopedia.com, Available here.
3. “Using UPDATE SQL Command.” Types of Network Topology in Computer Networks | Studytonight, Available here.
4. “What Is UPDATE? – Definition from Techopedia.” Techopedia.com, Available here.

Image Courtesy:

1. “SQL ANATOMY wiki” By :User:SqlPac, modified by Ferdna – File:Sql statement anatomy.png (CC BY-SA 3.0) via Commons Wikimedia