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

Question

我想一次更新两个表。下面的代码似乎工作正常。但是，在某些情况下，bidGroups中没有条目，这意味着整个语句将失败。我如何调整它，以便它更新第一位（watchedItems），并且如果watchedItems.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

Answer 1

以这种方式尝试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;

......