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.
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
UPDATE InvoiceDetails SET InvoiceDetails.VatRate = (SELECT Products.VatRate FROM Products WHERE Products.Product_ID = InvoiceDetails.Product_ID)