Updating a table from another table

2019-12-23 #sql

It's the end of the year, so I'm just going to translate an old post from 2007 that I still refer to once in a while: Mise à jour d'une table à partir d'une autre.

To update a column in a table from the equivalent column of another table, MS Access easily accepts two table names for the UPDATE command, but not SQL Server.

Example: after updating the VAT rates in the products table, you have to copy these revisions in the invoice details table.

With Access:

UPDATE InvoiceDetails, Products
SET    InvoiceDetails.VatRate = Products.VatRate
WHERE  InvoiceDetails.Product_ID = Products.Product_ID

With SQL Server:

UPDATE InvoiceDetails
SET    InvoiceDetails.VatRate = Products.VatRate
FROM   InvoiceDetails
INNER JOIN Products ON Products.Product_ID = InvoiceDetails.Product_ID

With Oracle:

UPDATE InvoiceDetails
SET    InvoiceDetails.VatRate = (SELECT Products.VatRate
                                 FROM   Products
                                 WHERE  Products.Product_ID = InvoiceDetails.Product_ID)