How to Update Table in SQL

Introduction

UPDATE query is used to update a record in a table.
UPDATE is a DML command, which operates on the data of the table and not on the structure of the table.
Using UPDATE Query, one can update all the records as well as update specific records within a table.
To update specific records within a table, UPDATE Query is used with the WHERE Clause.

UPDATE Query to change a single record in a table

We can use an UPDATE query with the WHERE clause to change one particular column value of an entire record.

Syntax:

UPDATE TABLE_NAME SET column_name = value WHERE[condition];

Example:

First we will create a database with name “employeedb”. Then in that database we will create a table “employee” and insert records into the table. We will consider this table and database for all the subsequent examples.

 mysql> USE employeedb;
 Database changed
 mysql> SELECT *FROM employee;
 +--------+----------+------------+
 | Emp_ID | Emp_Name | Emp_Salary |
 +--------+----------+------------+
 |      1 | Nikita   | 30000      |
 |      2 | Riddhi   | 25000      |
 |      3 | Nayan    | 45000      |
 |      4 | Shruti   | 15000      |
 |      5 | Anurati  | 50000      |
 +--------+----------+------------+
 5 rows in set (0.06 sec)

We will update the employee (Emp_ID = 3) with a new salary.

 mysql> UPDATE employee SET Emp_Salary = 40000 WHERE Emp_ID = 3;
 Query OK, 1 row affected (0.07 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 mysql> SELECT *FROM employee;
 +--------+----------+------------+
 | Emp_ID | Emp_Name | Emp_Salary |
 +--------+----------+------------+
 |      1 | Nikita   | 30000      |
 |      2 | Riddhi   | 25000      |
 |      3 | Nayan    | 40000      |
 |      4 | Shruti   | 15000      |
 |      5 | Anurati  | 50000      |
 +--------+----------+------------+
 5 rows in set (0.00 sec)

The salary of an employee (Emp_ID=3) is now changed to 40,000 from 45,000.

UPDATE Query to change multiple records in a table

We can use an UPDATE query with the WHERE clause to change multiple column values of a particular record.

Syntax:

UPDATE TABLE_NAME SET column_name1 = value1, column_name2 = value2...., column_nameN = valueN WHERE [condition];

Example:

We will update the employee (Emp_ID = 1) with a new salary and name.

 mysql> UPDATE employee SET Emp_Salary = 40000, Emp_Name = "Mayuri" WHERE Emp_ID = 1;
 Query OK, 1 row affected (0.07 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 mysql> SELECT *FROM employee;
 +--------+----------+------------+
 | Emp_ID | Emp_Name | Emp_Salary |
 +--------+----------+------------+
 |      1 | Mayuri   | 40000      |
 |      2 | Riddhi   | 25000      |
 |      3 | Nayan    | 45000      |
 |      4 | Shruti   | 15000      |
 |      5 | Anurati  | 50000      |
 +--------+----------+------------+
 5 rows in set (0.00 sec)

The salary and name of an employee (Emp_ID=1) is now changed to “40,000” and “Mayuri” respectively.

UPDATE Query to change all records in a table

We can use an UPDATE query omitting the WHERE clause to change one or more than one column values for all the records within a table.

Syntax:

UPDATE TABLE_NAME SET column_name1 = value1, [column_name2 = value2...., column_nameN = valueN];

Example:

We will update all the employees with a new salary and name.

 mysql> UPDATE employee SET Emp_Salary = 40000, Emp_Name = "Mayuri";
 Query OK, 5 rows affected (0.08 sec)
 Rows matched: 5  Changed: 5  Warnings: 0
 mysql> SELECT *FROM employee;
 +--------+----------+------------+
 | Emp_ID | Emp_Name | Emp_Salary |
 +--------+----------+------------+
 |      1 | Mayuri   | 40000      |
 |      2 | Mayuri   | 40000      |
 |      3 | Mayuri   | 40000      |
 |      4 | Mayuri   | 40000      |
 |      5 | Mayuri   | 40000      |
 +--------+----------+------------+
 5 rows in set (0.00 sec)

Since, we have not specified the condition in the query on the basis of which records are to be updated so, our query will change the column values of all the records present in a table. The salary and name of all the employees is now changed to “40,000” and “Mayuri” respectively.

How to Update Table in SQL?