Re: SQL: UPDATE values in two tables at once

[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;

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