Multi-Column Updates

How to update multiple columns in a table using another table as the source data.

Table data is updated using the UPDATE statement. This is easy if you just want to set a single column to a known value, as in:

UPDATE t1
SET c1 = 10

If you want to set multiple columns to new values at the same time, you could use this syntax:

UPDATE t1
SET c1 = 10,
c2 = 20

This is all well and good if you know what value you want to update the column to.If you do not know what the value should be, but want to use another table in the database as a source of data, then you need to use a sub-query of some sort. For single-column update operations, this is fairly standard across database systems, using a correlated sub-query of the following format:

UPDATE t1
SET c1 = (SELECT c2
FROM t2
WHERE t2.id = t1.id)

However, when it comes to multi-column updates using another table as the data source, the syntax changes depending on the RDBMS you are using. The syntax examples below all achieve the same result, but only on the database indicated. The syntax is not transportable between database systems.

Oracle

UPDATE t1
SET (c1, c2) = (SELECT c3, c4 FROM t2 WHERE t2.id = t1.id)

This is perhaps the simplest syntax to remember, as it is a straight extension to the single column syntax, still using a correlated subquery.

MS SQL Server

UPDATE t1
SET c1 = t2.c3,
c2 = t2.c4
FROM t2
WHERE t2.id = t1.id

This syntax merges the UPDATE and the SELECT statements together, bringing the FROM clause directly into the UPDATE .

PostgreSQL

UPDATE t1
SET c1 = t2.c3,
c2 = t2.c4
FROM t2
WHERE t1.id = t2.id;

This is the same syntax as used by MS SQL Server.

MySQL

UPDATE t1,t2
SET t1.c1 = t2.c3,
t1.c2 = t2.c4
WHERE t1.id = t2.id;

The syntax here is different again, and uses a JOIN syntax for the table being updated. While this may look less tidy than the other syntax above, it has the additional feature that columns from both t1 and t2 can be updated within the same statement.

SQLite

SQLite does not have a nice way to do this. The supported syntax for SQLite requires that each column be set to a value explicitly in the update statement. The way to achieve the result therefore requires two selects on the source table in order to achieve the update:

UPDATE t1 SET
c1 = (SELECT c3 FROM t2 WHERE t2.id = t1.id),
c2 = (SELECT c4 FROM t2 WHERE t2.id = t1.id)