SQL UPDATE Statement: A Complete Guide

Have you ever needed to update data that was already in a table? Learn how to do this with the SQL UPDATE Statement.

This post applies to Oracle, SQL Server, MySQL, and PostgreSQL.

What Is the SQL UPDATE Statement?

The SQL UPDATE statement allows you to change data that is already in a table in SQL.

The INSERT statement lets you add data to the table, and the DELETE statement lets you remove data from a table.

But the UPDATE statement changes the data in the table, without deleting it. It saves you having to delete and re-insert the data.

 

What is the SQL UPDATE Statement Syntax?

Basic UPDATE Statement

The syntax for the SQL UPDATE statement is:

UPDATE tablename
SET column1 = value1,
column2 = value2,
...
column_n = value_n
[WHERE condition];

The parameters are:

• tablename: The name of the table you want to update.
• column1/2/n: The column whose value you want to update.
• value1/2/n: The value or expression to use for the new value.
• condition: The condition in the WHERE clause to specify which rows to update. This is optional. If it is not provided, then all records in the table are updated.

 

Update with Subquery

The UPDATE statement allows you to update data from another table, using a SELECT statement. The syntax for this is:

UPDATE tablename
SET column = (SELECT query)
[WHERE condition];

The parameters are:

• tablename: The name of the table you want to update.
• column1/2/n: The column whose value you want to update.
• query: The SELECT query that returns the value you want to set the column to.
• condition: The condition in the WHERE clause to specify which rows to update. This is optional. If it is not provided, then all records in the table are updated.

You can find a complete guide to updating data with a SELECT statement in my guide here.

 

Update with Join

In some database vendors (SQL Server, MySQL, PostgreSQL), you are able to use a JOIN in an UPDATE statement to update data using values in another table.

UPDATE table1
SET table1.column = table2.column
FROM table1
INNER JOIN table2 ON table1.column2 = table2.column2
[WHERE condition];

The parameters are:

• table1: The name of the table you want to update.
• table2: the table being joined with to get other data
• column: The column whose value you want to update.
• query: The SELECT query that returns the value you want to set the column to.
• condition: The condition in the WHERE clause to specify which rows to update. This is optional. If it is not provided, then all records in the table are updated.

 

 

Update Specific Number of Rows

In SQL Server and MySQL, you can update a specified number of rows, even if there are more that specify the WHERE clause.

SQL Server:

UPDATE
[TOP (rowcount)]
tablename
SET column1 = value1
[WHERE condition];

MySQL:

UPDATE tablename
SET column1 = value1
[WHERE condition]
[LIMIT rowcount];

Both the TOP keyword in SQL Server and the LIMIT keyword in MySQL are optional.

 

SQL UPDATE Statement Examples

Let’s have a look at some examples of the SQL UPDATE statement. These examples apply to Oracle, SQL Server, MySQL, and PostgreSQL unless stated otherwise.

First, we’ll use some sample data in a table called student.

student_id	first_name	last_name	fees_paid	fees_required
1	John	Smith	100	500
2	Michelle	Jones	200	200
3	Sally	Brown	250	300
4	Peter	Black	0	200
5	Amanda	Green	500	500

 

Example 1 – Update a Single Column

We’ll update a single column and single row in this example.

UPDATE student
SET fees_paid = 100
WHERE student_id = 2;

This has updated the student record with a student_id of 2, and set the fees_paid value to 100.

 

Example 2 – Update Multiple Columns

You can update multiple columns in the same table in a single UPDATE statement.

UPDATE student
SET fees_paid = 500,
fees_required = 1000
WHERE student_id = 4;

This has updated both the fees_paid and fees_required fields for the student record with a student_id of 4. Note that there is a comma separating the fees_paid = 500 and the fees_required.

 

Example 3 – Update Using a Subquery

As mentioned in the syntax example earlier, you can use a subquery inside the UPDATE statement to set a value based on a query.

UPDATE student
SET fees_required = (
  SELECT MAX(fees_required)
  FROM student)
WHERE student_id = 5;

This example will update the fees_required value of the record with a student_id of 5. The value it will set it to is the MAX of the fees_required value for all records in the student table.

Notice that the WHERE clause is outside the brackets, meaning the condition applies to the UPDATE statement and not the SELECT statement.

 

Example 4 – Subquery in the WHERE Clause

You can also run an UPDATE statement and use a subquery in the WHERE clause.

UPDATE student
SET fees_paid = 400
WHERE fees_required = (
  SELECT MAX(fees_required)
  FROM student
);

This query will find the MAX of the fees_required field, find all rows that match that value, and update their fees_paid to 400.

 

Example 5 – Update Using an EXISTS in the WHERE Clause

You can use the EXISTS keyword in the WHERE clause along with a subquery. This is often a more efficient way of filtering (but not always).

UPDATE student
SET fees_paid = 350
WHERE EXISTS (
  SELECT student_id
  FROM address
  WHERE address.address_id = student.address_id
);

This will update the fees_paid value only where a matching value in the address table is found. This is just an example, but you can use the EXISTS function in a more meaningful way.

 

Example 6 – No WHERE Clause Specified

Because the WHERE clause is optional, you can run an SQL UPDATE statement without it. It will update every row in the table.

UPDATE student
SET fees_required = 100;

This will set the fees_required value to 100 for every record in the table. This is most likely not what you want to do – so be careful!

 

Example 7 – Update Using Inline View

Another way of updating only the values you want is by using an inline view.

Instead of specifying just the table, you can use a subquery, which is an inline view. This is also called an “update select”.

An example of this would be:

UPDATE (
  SELECT student_id,
  first_name,
  last_name,
  fees_paid,
  fees_required
  FROM student
  WHERE student_id = 2) std
SET std.fees_paid = 450;

This is another way of updating a single record using the Oracle SQL Update command.

 

Example 8 – Updating Two Columns Using a Subquery

You can also use a subquery to update two columns at once. This can be easier to read and write, especially for large queries.

UPDATE student s
SET (s.fees_paid, s.fees_required) = (
  SELECT MAX(fees_paid), MAX(fees_required)
  FROM student t
)
WHERE student_id = 6;

This will update both columns using the one SELECT subquery. It saves you having to write the query twice (once for each column) and therefore saves Oracle from running it twice.

 

Can You Update Multiple Tables in a Single UPDATE Statement?

No, you can’t. You can only update a single table in an UPDATE statement.

You’ll need to write separate statements to update a different table.

 

Why Is My SQL UPDATE Statement Not Working?

Your UPDATE statement might not be working for a variety of reasons. Some steps I usually take when I have issues with my UPDATE statement are:

Convert the UPDATE statement to a SELECT statement

Convert the statement to a SELECT and see which rows are returned. This will give you a preview of which rows will be changed.

If no rows are being returned, it means your UPDATE statement won’t update anything, and you won’t get the results you’re looking for.

Check the Data Types

You might be trying to set a value in a column where the data type doesn’t match.

Oracle

To find the data type of a column, you can run this command (replace YOUR_TABLE_NAME with the actual name of your table, in upper case):

SELECT column_name, data_type, data_length, data_precision
FROM SYS.USER_TAB_COLUMNS
WHERE table_name = 'YOUR_TABLE_NAME';

Then, check the data type of the value you’re setting the column to. You can run a SELECT statement on this value if it’s from a table, or look at it and see if it matches.

You could run a SELECT FROM DUAL on the value, and use the DUMP function to see the data type as well.

MySQL

You can query the information schema to get the data type of a column in a table:

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

SQL Server

You can query the information schema to get the data type of a column in a table:

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

PostgreSQL

You can query the information schema to get the data type of a column in a table:

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

 

Review the Logic

Perhaps you need to review the logic of your update statement if it’s not giving the results you are expecting.

Are you missing a join in a subquery? Are you selecting from the right field? Is your WHERE condition correct?

Double-check the query to make sure it’s correct.

Ask For a Code Review

If you still can’t get the UPDATE statement working, then perhaps you can ask a team member to review it.

Sometimes they can spot things you don’t because you’ve been looking at it for so long. That’s happened to me many times.

They might also know the system a bit better or might have had the issue before. In any case, asking a coworker for a review might help you correct your query.

 

MySQL LOW_PRIORITY Modifier and IGNORE Modifier

In the MySQL UPDATE statement, there are two keywords that can be used:

UPDATE [LOW_PRIORITY] [IGNORE] tablename
SET...

If you specify LOW_PRIORITY, the UPDATE statement is not run until there are no other connections reading from the table. This only applies if you are using storage engines that use table-level locking, such as MyISAM, MEMORY and MERGE.

If you specify IGNORE, the UPDATE statement does not abort even if you get an error during the update. If there is a duplicate key during the UPDATE process, the value is not updated. Updates that would cause data conversion errors are updated to the closest valid value instead.

 

Conclusion

So, that brings us to the end of the SQL UPDATE Statement guide. If you have any questions on the UPDATE statement, leave a comment below.