How do i update mulitple rows with dynamic data

sqlite

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.

Best Answer

UPDATE item 
SET Qty = Qty -
(SELECT sum(Bask_Qty) FROM user_basket 
 Where item.item_ID = user_basket.item_ID and user_basket.user_ID = 1);

This would update all items, though you may not notice it ( minus zero! ). Better add your

WHERE item_ID IN (SELECT item_ID FROM user_basket WHERE user_ID = 1);
Related Question