CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
Gireesh Pai
Gireesh Pai

Reputation: 87

Using update Query for updating multiple rows with calculations

I have 2 tables a "product" table

**prod_cat**     **prod_code**    **prod_name**   **prod_desc** **prod_price** **prod_stock**

MB01 MB01_micro ASU M5A_M Asus mobo 7500 enter image description here45

PRO01 PRO01_01 INT_i3_7300 Intel core i3 Processor 9313 5

and another table "cart" table 

id cart_cust_id  cart_prod_id  cart_qty cart_prod_total cart_status
1   xxxx            ASU M5A_M     1        7500            NOT PAID
2   xxxx            INT_i3_7300   2        18626           NOT PAID

What I need is a query or a procedure to reduce the "prod_stock" according to the value in "cart_qty". Any suggestions ?

I have also provided the screenshots of the 2 tables in the links.Screen shots of the tables

My update statement:

UPDATE product 
  SET prod_stock = ((SELECT cart_quantity 
                     FROM cart 
                     WHERE prod_code LIKE cart_prod_id 
                       AND cart_cust_id LIKE '[email protected]' 
                       AND cart_status LIKE 'NOT PAID') - prod_stock)

Gives an error:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Upvotes: 0

Views: 106

Answers (2)

St&#233;phane CLEMENT
St&#233;phane CLEMENT

Reputation: 372

With no tempdb use (faster way) :

UPDATE P
SET
    P.prod_stock = P.prod_stock - C.cart_quantity
FROM
    Product AS P
    INNER JOIN CART AS C ON (C.cart_prod_id = P.prod_code)

You can add a WHERE clause :

UPDATE P
SET
    P.prod_stock = P.prod_stock - C.cart_quantity
FROM
    Product AS P
    INNER JOIN CART AS C ON (C.cart_prod_id = P.prod_code)
WHERE *XXX* = *YYY* AND ...

Upvotes: 1

Abdulkadir Erkmen
Abdulkadir Erkmen

Reputation: 202

You can join the tables and 

 ;WITH T AS(
    Select * FROM Product P 
    INNER JOIN CART C ON C.cart_prod_id = P.prod_code
    )
    Update T SET prod_stock = prod_stock - cart_quantity

Upvotes: 1

Related Questions