✔ 最佳答案
可以用一句 sql statement 將 stock table 由 shopping cart table update。
假設 shopping_cart table 如下
id name quantity price user_id
For SQL Server:
update stock set quantity = stock.quantity - shopping_cart.quantity
from stock, shopping_cart
where stock.name = shopping_cart.name
and shopping_cart.user_id = 12345
For MS Access:
update stock set quantity = stock.quantity - shopping_cart.quantity
from stock inner join shopping_cart
on stock.name = shopping_cart.name
where shopping_cart.user_id = 12345
Generic sql statement 適用於大多數 database
update stock set quantity = quantity - (select quantity from shopping_cart
where shopping_cart.name = stock.name
and shopping_cart.user_id = 12345)
通常 shopping cart table 都是 dynamically 建立,每個 user 一個 table,如 user1_shopping_cart,以上 sql statements 就可除去
user_id = 12345
update stock table 後就要 remove shopping_cart records 或將 shopping_cart records mark 了以作識別。