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


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

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: