AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

Update records in a table using criteria from another table

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Update records in a table using criteria from another table

Update the products table using criteria from the suppliers table

Discussion:

In this example, our goal is to update prices in the tbls_products_Upd table using criteria from the suppliers table.  We can achieve this using a subquery.  The subquery will retrieve supplier ids from the suppliers table and will update the corresponding product prices in the tbls_products_Upd table.  Specifically, the subquery will retrieve supplier ids for suppliers in Boston or Dallas and feed those ids in the WHERE clause of the main update statement. 

Let me explain this point a bit more.  The business request is to update product prices for suppliers in Boston and Dallas.  We might be facing increased shipping costs from those locations, and since management does not understand supplier ids, they tell us to increase prices by 20% for everything coming from that direction.  Our problem is that the tbls_products_Upd table does not contain any city information.  We can satisfy management and our technical needs by using the subquery below: 

Code:

UPDATE tbls_Products_Upd

SET ProductUnitPrice = ProductUnitPrice * (1+0.20)

WHERE SupplierID IN

(SELECT SupplierID FROM Suppliers

WHERE city= "Boston" or city = "Dallas")

 

Result: