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


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com