Re: SQL: UPDATE values in two tables at once

Hello Charles,

thanks, I didn't know about MERGE.

Am 06.05.2020 um 16:44 schrieb Charles Wilt <charles.wilt@xxxxxxxxx>:

While a subselect would work, now that MERGE is available, consider that..

merge into table1 dst
using (select id, alert from table2) src
on dst.id = src.id
when matched and dst.stamp <= src.alert
then update dst.status = 'OLD'
else ignore;

merge into table1 dst
using (select id, alert from table2) src
on (dst.id = src.id)
when matched
and dst.stamp <= (DATE(CURRENT_TIMESTAMP) - src.alert days)
then update dst.status = 'Old'
else ignore

This complains about dst being invalid (after "then update"). After studying https://www.mcpressonline.com/analytics-cognitive/db2/sql-merge-statement-tricks I can't see what's wrong.

Meanwhile, I'll try Birgittas solution.

:wq! PoC

PGP-Key: DDD3 4ABF 6413 38DE - https://www.pocnet.net/poc-key.asc