SQL - UPDATE Query

The SQL UPDATE Query is used to modify the existing records in a table. This statement is a part of Data Manipulation Language, as it only modifies the data present in a table without affecting the table s structure.

Since it only interacts with the data of a table, the UPDATE statement needs to used cautiously. If the rows to be modified aren t selected beforehand, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted. Therefore, to filter records that needs to be modified, you can use a WHERE clause. Using a WHERE clause, you can either update a single row or multiple rows.

The UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.

Syntax

The basic syntax of the UPDATE query with a WHERE clause is as follows −

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using the AND or the OR operators.

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS
SET ADDRESS =  Pune 
WHERE ID = 6;

Output

The query produces the following output −

(1 row affected)

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would have the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Updating Multiple Rows and Columns

Using UPDATE statement, multiple rows and columns in a table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it.

However, to update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns.

Syntax

Following is the syntax to update multiple rows and columns −

UPDATE table_name
SET column_name1 = new_value, column_name2 = new_value...
WHERE condition(s)

Example

If you want to modify all the ADDRESS and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough as shown in the following code block.

UPDATE CUSTOMERS
SET ADDRESS =  Pune , SALARY = 1000.00;

Output

The query produces the following output −

(7 rows affected)

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  |
+----+----------+-----+---------+---------+
|  1 | Ramesh   |  32 | Pune    | 1000.00 |
|  2 | Khilan   |  25 | Pune    | 1000.00 |
|  3 | kaushik  |  23 | Pune    | 1000.00 |
|  4 | Chaitap |  25 | Pune    | 1000.00 |
|  5 | Hardik   |  27 | Pune    | 1000.00 |
|  6 | Komal    |  22 | Pune    | 1000.00 |
|  7 | Muffy    |  24 | Pune    | 1000.00 |
+----+----------+-----+---------+---------+

Example

But if you want to modify only few records of the ADDRESS and the SALARY column values in the CUSTOMERS table, you need to specify a condition in the WHERE clause such that only those few records will satisfy it as shown in the following query −

UPDATE CUSTOMERS
SET ADDRESS =  Pune , SALARY = 1000.00
WHERE NAME =  Ramesh ;

Output

This query produces the following output −

(1 rows affected)

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Pune      |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+