The Linux Page

UPDATE using multiple tables

Today I wanted to copy a column from one table to another. One way is to go through the table and update the items one by one. The other way is to use the UPDATE order a copy the data as required.

The UPDATE command does NOT support such a feature by default and thus each database has its own syntax when it comes to how to make that work.

PostgreSQL uses an extra FROM between the SET and WHERE orders.

  UPDATE destination-table SET column = value FROM extra-tables WHERE conditions

MySQL accepts multiple tables in the first list of tables. The only problem with that syntax is that it isn't clear what table will be updated.

  UPDATE tables SET column = value WHERE conditions

MS-Access has that feature and of course it is NOT documented in the MS-Access help. I have a hard time with that since it is available, it is a great feature so why not document it?!

  UPDATE table INNER JOIN extra-table ON conditions SET column = value WHERE conditions

This probably works on multiple tables, although I only needed to update table A.col from table B.col. So one INNER JOIN was sufficient for me.

The one reason why it is very useful in MS-Access is that this way I can work in an MS-Access table and later copy the result in an external database (i.e. some ODBC table) without having to access the ODBC table once per change. Overall, it makes it faster (although it still is quite slow!)