使用可能的空值更新非空字段

Question

在下面的查询中：

update collect_irc_deploy c
set hid = (select id
from auth_hierarchy
where fqdn = (select location
from reserve
where id=c.rid
)
)
where hid = 0 and rid is not null

子查询select id from auth_hierarchy where fqdn = (select location from reserve where id = c.rid)可能会在字段hidNOT NULL时返回NULL。

如何修改语句，以便在子查询返回 NULL 时跳过该数据项，而不是使整个执行失败？

Answer 1

您可以使用更新...联接语法，以确保仅更新联接的行：

update collect_irc_deploy
join reserve on reserve.id = collect_irc_deploy.rid
join auth_hierarchy on auth_hierarchy.fqdn = reserve.location
set collect_irc_deploy.hid = auth_hierarchy.id
where collect_irc_deploy.hid = 0 and collect_irc_deploy.rid is not null

Answer 2

使用UPDATE IGNORE解决了我的问题。 但它会生成警告消息。