mysql update問題.........

2008-04-29 7:58 am

$query = "Select * from shopping where sid='$sid'";
$result = mysql_query($query);
$shopping = mysql_fetch_object($result);
$query = "update stock set quantity = quantity - (select qty from shopping
where shopping.name = stock.name)";
mysql_query($query);

我用以上段code去做update,成功左一半,而另一半問題就係,當我update完之後果一樣貨品以下既貨品既數量就會變左我set既預設值....
eg:
stock的table:
id name quantity price <-field名
1....pen..........10.......7
2....abc..........10.......5
3....cde..........10.......8
4....fgh..........10........9

shopping cart的table:
sid name qty price <-field名
..1....pen...1........7
..2....cde...1........7

stock的table update後:
id name quantity price <-field名
1....pen...........9.......7
2....abc..........00......5 (00是預設值)
3....cde...........9.......8
4....fgh..........00.......9 (00是預設值)

有沒有人知道為什麼會這樣?

回答 (1)

garlic2010

2008-04-29 8:58 am

✔ 最佳答案

因為如果 subquery 是 no match 就變成

update stock set quantity = quantity - null

所以沒有 update 的記錄都變預設值。解決方法:

update stock set quantity = quantity - (select qty from shopping
where shopping.name = stock.name)
where EXISTS (select 'anything' from shopping
where shopping.name = stock.name)

where EXISTS 是要測試有沒有 matching record，有 matching records 才 update。

where EXISTS (select null from shopping
where shopping.name = stock.name) 是一樣結果。

2008-04-29 01:03:18 補充：
前一個問題
http://hk.knowledge.yahoo.com/question/question?qid=7008042700469
SQL Server 的方法就沒有這個問題

update stock set quantity = stock.quantity - shopping.quantity
from stock, shopping
where stock.name = shopping.name

👍 0 👎 0