一次针对两个表的条​​件SQL更新语句 [英] A conditional SQL update statement for two tables at once

问题描述

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

I want to update two tables at once. The code below seems to work fine. However, in some cases, there is no entry in bidGroups which means the entire statement would fail. How can I adjust it so it updates the first bit (watchedItems) and does not try the second part if watchedItems.bidGroupID IS 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;

我尝试过，但是语法错误.

I tried this, but the syntax is wrong..

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

推荐答案

尝试以这种方式向左加入:

Try LEFT JOIN this way:

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;

......

这篇关于一次针对两个表的条​​件SQL更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！