Updating a table from another table

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)

---

This post was originally published on my blog.
Cover image : dbdiagram.io.

Comments

[Akonibrahim]

Did you used any tool to generate the diagram of the tables?

[Michel]

It's a screenshot of the website dbdiagram.io, a free online tool to draw ER diagrams.

[Akonibrahim]

Pretty cool thank you