'Update non-null field with possibly null values

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?

mysqlsqlsql-update


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Salman A
Solution 2 xrfang

Related Questions