CODEWITHSUNDEEP
sqlite
randomguy
randomguy

Reputation: 69

how do i update mulitple rows with dynamic data?

I am using SQLite to update mulipte rows, but it does not work.

item table to keep the item data

item_ID Qty
-       -
1      10
2      10
3      10

user_basket table to keep users' basket data

user_ID item_ID  Bask_Qty 
-        -       - 
1        1       5
1        2       1
2        1       1

I used command like:

UPDATE item 
SET Qty = 
(SELECT Qty-Bask_Qty FROM user_basket 
INNER JOIN item ON item.item_ID = user_basket.item_ID) 
WHERE item_ID IN (SELECT item_ID FROM user_basket WHERE user_ID = 1);

After the command, I should expect item table be like that:

item_ID Qty
-       -
1      5
2      9
3      10

but instead, I got:

item_ID Qty
-       -
1      5
2      5
3      10

apparently, it used the same value to update all the rows.

Upvotes: 1

Views: 71

Answers (1)

forpas
forpas

Reputation: 164089

Use a correlated subquery:

UPDATE item AS i
SET Qty = i.Qty - COALESCE(
  (SELECT SUM(b.Bask_Qty) FROM user_basket b WHERE b.user_ID = 1 AND b.item_ID = i.item_ID), 
  0
)

See the demo.

Or, with a WHERE clause to avoid unnecessary updates:

UPDATE item AS i
SET Qty = i.Qty - (SELECT SUM(b.Bask_Qty) FROM user_basket b WHERE b.user_ID = 1 AND b.item_ID = i.item_ID)
WHERE EXISTS (SELECT 1 FROM user_basket b WHERE b.user_ID = 1 AND b.item_ID = i.item_ID)

See the demo.

If your version of SQLite is 3.33.0+ you can use UPDATE...FROM syntax:

UPDATE item AS i
SET Qty = i.Qty - b.Bask_Qty 
FROM (SELECT item_ID, SUM(Bask_Qty) Bask_Qty FROM user_basket WHERE user_id = 1 GROUP BY item_ID) AS b
WHERE b.item_id = i.item_id

In all of the above queries I used SUM(Bask_Qty) to return the quantity of each item from the table user_basket, just in case there are more than 1 rows for each item.
If you are sure that there may be only 1 row for each item then replace it with just Bask_Qty.

Upvotes: 2

Related Questions