×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
