Binaryworld - Updating a table from another table ... [ SQL Server -> T-SQL ]

As the article title suggests, an UPDATE statement is able to reference data in other tables. This is done using a FROM clause.
Let's use the Northwind database as our example here. A usual update statment to update a product's list price might look something like this.

Click here to copy the following block

UPDATE Products
SET UnitPrice = 195.99
WHERE
ProductID = 14

(That is some really expensive Tofu)

This works fine but it is not too likely you will do all price changes via Query Analyzer. A more realistic example when running your hugely successful online store might be where you get a weekly CSV file with updated prices for your products.

You can DTS this file into a holding table (I provided a script to create holding table and simulate price data) and then run this:

Click here to copy the following block

Update Products
SET Products.UnitPrice = Holding.UnitPrice
FROM
Holding
WHERE
Products.ProductID = Holding.ProductID

What this does is updates the products table with data from the holding table where the product id's match.

Now let's say that your supplier's product id doesn't match your id (isn't that always the way), but the product name and supplier name does. Here you are going to have to join your supplier table to your products table to compare them.

Click here to copy the following block

Update Products
SET Products.UnitPrice = Holding.UnitPrice
FROM
Products
INNER JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID
INNER JOIN Holding ON Holding.Supplier = Suppliers.CompanyName
AND Holding.Product = Products.ProductName

This joins the Suppliers, Products and Holding Tables together and then updates the product price with the holding price.

Good luck and happy coding.