mysql - 一次对两个表的条​​件 SQL 更新语句

我想一次更新两个表。下面的代码似乎工作正常。但是，在某些情况下，bidGroups 中没有条目，这意味着整个语句将失败。我如何调整它以便它更新第一位(watchedItems)并且如果 watchItems.bidGroupID 为 NULL 则不尝试第二部分

UPDATE watchedItems, bidGroups
SET watchedItems.won=1, bidGroups.bidGroupQty=bidGroups.bidGroupQty-1
WHERE watchedItems.id=2
AND watchedItems.aid=200618152822
AND bidGroups.bidGroupID=watchedItems.bidGroupID
AND bidGroups.id=2;

我试过这个，但语法是错误的..

UPDATE  watchedItems, bidGroups
SET watchedItems.won=1,
CASE WHEN watchedItems.bidGroupID IS NOT NULL THEN bidGroups.bidGroupQty=bidGroups.bidGroupQty-1
ELSE END
WHERE watchedItems.id=2
AND watchedItems.aid=200618152822
AND bidGroups.bidGroupID=watchedItems.bidGroupID
AND bidGroups.id=2

最佳答案

尝试 LEFT JOIN 这种方式:

UPDATE watchedItems
LEFT JOIN bidGroups ON watchedItems.bidGroupID = bidGroups.bidGroupID AND bidGroups.id=2
SET watchedItems.won=1, bidGroups.bidGroupQty=bidGroups.bidGroupQty-1
WHERE watchedItems.id=2
AND watchedItems.aid=200618152822;

......

关于mysql - 一次对两个表的条​​件 SQL 更新语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6362594/