Re: SQL: UPDATE values in two tables at once

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;

Charles

On Wed, May 6, 2020 at 8:29 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

Hello,

I'm struggling to port a working MySQL UPDATE statement to IBM i (7.2)
SQL. I already fixed the totally different time calculations:

UPDATE table1, table2
SET table1.status='Old'
WHERE table1.stamp <= table2.alert days
AND table1.id=table2.id

How am I supposed to tell SQL that the update (in table1) should only be
done when there's an entry in table2 with matching ID?

The above variant gives: SQL0104 Token , was not valid. Valid tokens: SET

When leaving out the second table, I get: SQL0206 - Column or global
variable ALERT not found. The second table isn't listed and thus it's
content isn't known.

Trying to apply the LEFT JOIN syntax: SQL0199 - Keyword LEFT not expected.
Valid tokens: SET.

Im not sure if a subSELECT with a JOIN like this will do the right thing
(not tested, wanted to have opinions first, because it seems like an
indirect doubled constraint, with JOIN…ON and WHERE):

UPDATE table1, table2
SET table1.status='Old'
WHERE table1.stamp <= (
SELECT alert FROM table2
RIGHT JOIN table1 on ON (table1.id=table2.id)
WHERE table1.id=table2.id)
days

May I kindly ask for a nudge in the right direction? Google turned up
nothing helpful specifically to this requirement.

Thank you!

: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