用sql甚樣才可以在shoppingcart check out後做update貨品的數量!?

2008-04-27 1:00 pm
我有兩個table: 一個係stock, 一個係shopping cart
shopping cart checkout table之後甚樣update stock table貨品中的數量?
eg:
stock的table:
id name quantity price <-field名
1....pen..........10.......7

shopping cart的table:
id name quantity price <-field名
1....pen............2........7

stock的table update後:
id name quantity price <-field名
1....pen............8........7
更新1:

而且shopping cart table不只一樣貨品

回答 (2)

2008-04-28 12:13 am
✔ 最佳答案
可以用一句 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 了以作識別。
2008-04-28 7:41 am
garlic兄方法比我更好.


收錄日期: 2021-04-25 20:33:56
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20080427000051KK00469

檢視 Wayback Machine 備份