Update non-null field with possibly null values-CodePudding

In the query below:

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

the subquery select id from auth_hierarchy where fqdn = (select location from reserve where id = c.rid) may return NULL while the field hid is NOT NULL.

How can I modify the statement so that if the subquery returns NULL that data item is skipped instead of failing the entire execution?

CodePudding user response：

Use UPDATE IGNORE solved my problem. But it will generate warning messages.

CodePudding user response：

You can use update...join syntax to ensure only joined rows are updated:

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