SQL UPDATE Statement

The SQL UPDATE statement is used to update (modify) the existing records in tables.

We often use a conditional UPDATE statement to specify which records will be updated.

The WHERE clause is used to restrict the update to affect only the specified records.

Without making any restriction, the UPDATE statement will modify all the records for a table's specific columns.

Syntax

The syntax of the UPDATE statement is as the following:

UPDATE table_name
SET column1_name = value1, column2_name = value, ...
[WHERE conditions];

Parameters:

• column1_namen, column2_name: The column that you want to update.
• value1, value2 : The new values to assign to the column1_name, and column2_name.
• WHERE conditions: Optional. The conditions that must be satisfied for the update to execute.

Note: The WHERE clause in the UPDATE statement restricts the update on specific rows according to the condition. If the WHERE clause is omitted, all the records will be updated.

Demo Table

Let us suppose that we have a table called "employee" with the following records:

UPDATE Data in a Table Example

The following SQL statement will update the "first_name," and the "last_name" filed of the "employee" table and set a new value where the "emp_no" is equal to 1006.

UPDATE employee
SET first_name = 'Richard', last_name='Garcia'
WHERE emp_no = 1006;

After the execution, the selection from the "employee" table will look like this:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | Richard     | Garcia    | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

UPDATE Multiple Records Example

It is the conditions of the WHERE clause that determines which records will be updated.

The following SQL statement will update the "last_name" filed of the "employee" table to "Nelson" for all records that have "Development" as a department.

UPDATE employee
SET last_name = 'Nelson'
WHERE dept_name = 'Development';

After running the above SQL statement, the selection from the "employee" table will look as follows:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Nelson    | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Nelson    | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | Richard     | Garcia    | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

UPDATE Without WHERE clause

You need to be careful when you are using the UPDATE statement. Because if you omit the WHERE clause, all records in the specified table will be updated.

The following SQL statement will update the "dept_name" field of the "employee" table to "Marketing" for all the records in the table.

UPDATE employee
SET dept_name = 'Marketing';

After the execution, the selection from the "employee" table will look like this:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Marketing        |
| 1002   | Maria       | Nelson    | 1993-06-13 | 2018-10-08 | 9000      | Marketing        |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Marketing        |
| 1004   | Robert      | Nelson    | 1995-09-07 | 2019-09-08 | 9600      | Marketing        |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Marketing        |
| 1006   | Richard     | Garcia    | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Marketing        |
+--------+-------------+-----------+------------+------------+-----------+------------------+