IMHO the query must be changed as follows:
UPDATE table1 a
SET table.status='Old'
Where Exists(Select 1
From BACHUPMON.CONFIGPF b
Where a.Id = b.Id
and a.Stamp <= Current_Date - b.Alert Days)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Patrik
Schindler
Sent: Donnerstag, 7. Mai 2020 12:45
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL: UPDATE values in two tables at once
Hello Kevin,
Am 06.05.2020 um 16:41 schrieb Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx>:
I usually use where exists because I'm used to it, but I think there are
better ways out there. Something like this maybe.
Thank you very much!
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)
I struggle to incorporate the date calculation.
UPDATE table1 SET table.status='Old'
WHERE table1.stamp <= DATE(CURRENT_TIMESTAMP) - (
SELECT table2.alert FROM table2
) days AND EXISTS
(
SELECT 1 FROM backupmon.configpf
WHERE b_statuspf.id=configpf.id
)
This one is not throwing a syntax error but complaining that the result of
the statement yields more than one line. Hmm.
Any hints?
: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
midrange.com
