SQL UPDATE

Summary: in this tutorial, you will learn how to use the SQL UPDATE statement to modify data of the existing rows into a table.

Introduction to the SQL UPDATE statement

To change existing data in a table, you use the UPDATE statement. The following shows the syntax of the UPDATE statement:

UPDATE table_name
SET column1 = value1,
 column2 = value2
WHERE
	condition;

In this syntax:

• First, indicate the table that you want to update in the UPDATE clause.
• Second, specify the columns that you want to modify in the SET clause. The columns that are not listed in the SET clause will retain their original values.
• Third, specify which rows to update in the WHERE clause.

The UPDATE statement affects one or more rows in a table based on the condition in the WHERE clause.  For example, if the WHERE the clause contains a primary key expression, the UPDATE statement changes one row only.

However, any row that causes the condition in the WHERE to evaluate to true will be modified. Because the WHERE clause is optional, therefore, if you omit it, then all the rows in the table will be affected.

SQL UPDATE statement examples

We will use the employees and dependents table to demonstrate the UPDATE statement.

SQL UPDATE one-row example

Suppose the employee id 192 Sarah Bell changed her last name from Bell to Lopez and you need to update her record in the  employees table.

To update Sarah’s last name from  Bell to Lopez, you use the following UPDATE statement:

UPDATE employees 
SET 
    last_name = 'Lopez'
WHERE
    employee_id = 192;

The database system updated the value in the last_name column and the row with  employee_id 192.

You can verify it by using the following SELECT statement.

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	employee_id = 192;

SQL UPDATE multiple rows examples

Now, Nancy wants to change all her children’s last names from Bell to Lopez. In this case, you need to update all Nancy’s dependents in the dependents table.

Before updating the data, let’s check the dependents of Nancy.

SELECT
	*
FROM
	dependents
WHERE
	employee_id = 192;

To update the last names of Nancy’s dependents, you use the following UPDATE statement.

UPDATE dependents 
SET 
    last_name = 'Lopez'
WHERE
    employee_id = 192;

SQL UPDATE with subquery example

Sometimes when employees change their last names, you update the  employees table only without updating the dependents table.

To make sure that the last names of children are always matched with the last name of parents in the  employees table, you use the following statement:

UPDATE dependents
SET last_name = (
	SELECT
		last_name
	FROM
		employees
	WHERE
		employee_id = dependents.employee_id
);

Because the WHERE the clause is omitted, the UPDATE statement updated all rows in the dependents table.

In the SET clause, instead of using the literal values, we used a subquery to get the corresponding last name value from the  employees table.

In this tutorial, we have shown you how to use the SQL UPDATE statement to modify existing data in a table.