DEV Community

Cover image for Updating a table from another table
Michel
Michel

Posted on • Updated on • Originally published at blog.pagesd.info

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
Enter fullscreen mode Exit fullscreen mode

With SQL Server:

UPDATE InvoiceDetails
SET    InvoiceDetails.VatRate = Products.VatRate
FROM   InvoiceDetails
INNER JOIN Products ON Products.Product_ID = InvoiceDetails.Product_ID
Enter fullscreen mode Exit fullscreen mode

With Oracle:

UPDATE InvoiceDetails
SET    InvoiceDetails.VatRate = (SELECT Products.VatRate
                                 FROM   Products
                                 WHERE  Products.Product_ID = InvoiceDetails.Product_ID)
Enter fullscreen mode Exit fullscreen mode

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

Discussion (3)

Collapse
akonibrahim profile image
Akonibrahim

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

Collapse
michelc profile image
Michel Author

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

Collapse
akonibrahim profile image
Akonibrahim

Pretty cool thank you