I usually use where exists because I'm used to it, but I think there are better ways out there. Something like this maybe.
update table 1 set table1.status = 'Old'
where table1.stamp <= (select table2.alert from table2)
and exists (select 1 from table2 where table1.id = table2.id)
On Wed, 2020-05-06 at 16:29 +0200, Patrik Schindler 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>
https://www.pocnet.net/poc-key.asc
[
https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com
CONFIDENTIALITY NOTICE
This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.
midrange.com
