Serge Rielau wrote:
Bob Stearns wrote:
I need to UPDATE tablea only when JOINed tableb and tablec have
certain properties. Due to a severe shortcoming on my part, I can not
picture a syntax which will allow this. Some help would be
appreciated. Examples, pointers to either relevant manual pages or
examples, etc. I have looked at SQL Reference Vol 2 without
enlightenment.
MERGE INTO tablea AS D
USING (SELECT ...... FROM tableb, tablec WHERE....) AS S
WHEN MATCHED THEN UPDATE SET T.c1 = S.blah, ....
Cheers
Serge
Thank you for the suggestion. I must remember to think "MERGE" whenever
"UPDATE" is under discussion.
The two forms I now have are:
UPDATE is3.animals SET activex='N',
bh_disposal_code=88,
disposal_date=CURRENT_DATE
WHERE EXISTS (SELECT 1
FROM is3.animals t1
JOIN is3.et_implants t2 on t2.bhid=t1.bhid
WHERE t1.dam_bhid=123123
AND t1.birth_date is null)
AND dam_bhid=123123
AND birth_date is null
go
MERGE INTO is3.animals mi
USING (SELECT t1.bhid
FROM is3.animals t1
JOIN is3.et_implants t2 on t2.bhid=t1.bhid
WHERE t1.dam_bhid=123123
AND t1.birth_date is null) as mu
ON mi.bhid=mu.bhid
WHEN MATCHED THEN UPDATE SET activex='N',
bh_disposal_code=88,
disposal_date=CURRENT_DATE
GO