CODEWITHSUNDEEP
sqlms-access
aurelio
aurelio

Reputation: 207

SQL Update Statement

I have 2 tables, Products and ShoppingCart and I would like to update and decrease the product's 'Quantity' of the Products table based on the Product name and Quantity specified in the ShoppingCart. How can i do this?

Table: Products Fields: ProductName, ProductQty

Table: Shopping Cart Fields: ProductName, ProductQty

Access DB

Upvotes: 0

Views: 242

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

You should have a product id. However:

 UPDATE Products p
 INNER JOIN [Shopping Cart] s
 ON p.[Product Name] = s.[Product Name]
 SET p.ProductQty = p.ProductQty - s.ProductQty

You should get rid of spaces in field and table names, as well.

Upvotes: 2

Related Questions