SQL Update statement

The UPDATE statement is used to update the records of an existing table in a database. We can update single as well as multiple columns using SQL update statement.

The basic syntax of UPDATE statement with WHERE clause is −

UPDATE table_name
SET column1=value, column2=value2,...
WHERE condition;

We can combine N numbers of condition using "AND" or "OR" operators.

Consider the Employee table having the following records −

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Kamal    |  32 | Indore    |  5000.00 |
    |  2 | Roshan   |  25 | Indore    |  1800.00 |
    |  3 | Shyam    |  23 | Delhi     |  2000.00 |
    |  4 | Sagar    |  25 | Mumbai    |  6500.00 |
    |  5 | Komal    |  27 | Bhopal    |  8500.00 |
    +----+----------+-----+-----------+----------+

The following SQL statement will update the ADDRESS for a employee whose ID is 5 in the table.

SQL> UPDATE Employee
           SET ADDRESS = 'Pune'
           WHERE ID = 5;

The selection from the "Employee" table will now look like this:-

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Kamal    |  32 | Indore    |  5000.00 |
    |  2 | Roshan   |  25 | Indore    |  1800.00 |
    |  3 | Shyam    |  23 | Delhi     |  2000.00 |
    |  4 | Sagar    |  25 | Mumbai    |  6500.00 |
    |  5 | Komal    |  27 | Pune      |  8500.00 |
    +----+----------+-----+-----------+----------+

UPDATE Multiple Records

The following SQL statement will update the SALARY to "3000.00" for all records where ADDRESS is "Indore":

SQL> UPDATE Employee
           SET SALARY=3000.00
           WHERE ADDRESS='Indore';

The selection from the "Employee" table will now look like this:-

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Kamal    |  32 | Indore    |  3000.00 |
    |  2 | Roshan   |  25 | Indore    |  3000.00 |
    |  3 | Shyam    |  23 | Delhi     |  2000.00 |
    |  4 | Sagar    |  25 | Mumbai    |  6500.00 |
    |  5 | Komal    |  27 | Pune      |  8500.00 |
    +----+----------+-----+-----------+----------+

Update Warning!

Be careful when updating records. If we forgot to put WHERE clause, ALL records will be updated!

SQL> UPDATE Employee
           SET ADDRESS='Pune';

The selection from the "Employee" table will now look like this:-

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Kamal    |  32 | Pune      |  3000.00 |
    |  2 | Roshan   |  25 | Pune      |  3000.00 |
    |  3 | Shyam    |  23 | Pune      |  2000.00 |
    |  4 | Sagar    |  25 | Pune      |  6500.00 |
    |  5 | Komal    |  27 | Pune      |  8500.00 |
    +----+----------+-----+-----------+----------+