SQL UPDATE: Modify Existing Data in a Table By Examples (2024)

Summary: in this tutorial, you will learn how to use the SQL UPDATE statement to modify data of the existing rows 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_nameSET column1 = value1, column2 = value2WHEREcondition;Code language: SQL (Structured Query Language) (sql)

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 clause contains a primary key expression, the UPDATE statement changes one row only.

However, any row that causes the condition in theWHEREto evaluate to true will be modified. Because the WHERE clause is optional, therefore, if you omit it, the 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;Code language: SQL (Structured Query Language) (sql)

Try It

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

You can verify it by using the following SELECT statement.

SELECTemployee_id,first_name,last_nameFROMemployeesWHEREemployee_id = 192;Code language: SQL (Structured Query Language) (sql)

Try It

SQL UPDATE multiple rows example

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*FROMdependentsWHEREemployee_id = 192;Code language: SQL (Structured Query Language) (sql)

Try It

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;Code language: SQL (Structured Query Language) (sql)

Try It

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 dependentsSET last_name = (SELECTlast_nameFROMemployeesWHEREemployee_id = dependents.employee_id);Code language: SQL (Structured Query Language) (sql)

Try It

Because the WHERE 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.