对于联接表中不存在的行，如何将空值更新为0

Question

我有两个表。

requests
id|code|count|

oldrequests
id|code|count|

我需要从旧请求更新请求中的计数。我试图用聚结(oldrequests.count，0(更新NULLcount值的主要问题如下：

update request req
set count = coalesce(oldreq.count,0)
from oldrequests oldreq
where req.code = oldreq.code

但是中的NULL值请求不更新。然后我尝试将NULL值设置为0，如下所示：

update request req
set count = 0
from oldrequests oldreq
where req.code = oldreq.code and oldreq.id is null

但这对我也没有帮助。如何更新值和NULL值？

Answer 1

我的意思是，问题是oldrequests表中的行不匹配的更新计数。我可以建议下一种解决方法：

通过0更新requests表中的所有计数，更新后匹配oldrequests表中的行计数，如：

update requests set count = 0;
update requests req
set count = coalesce(oldreq.count,0)
from oldrequests oldreq
where req.code = oldreq.code;

另一种方法是为不匹配的行在ly上设置0：

update requests set count = 0
where (select code from oldrequests where oldrequests.code = requests.code) is null;

db<gt；小提琴

或使用not exist子句的另一个版本：

update requests set count = 0
where not exists (
select code from oldrequests where oldrequests.code = requests.code
);

db<gt；小提琴