469,327 Members | 1,292 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

How to UPDATE with joins

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.
Nov 12 '05 #1
4 1351

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.


Not sure, but do you mean:

update tablea a set ...
where exists (
select 1 from tableb b where a.... = b....
) and exists (
select 1 from tablec c where a.... = c....
)
/Lennart

Nov 12 '05 #2
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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3
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
Nov 12 '05 #4
le*****@kommunicera.umea.se 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.

Not sure, but do you mean:

update tablea a set ...
where exists (
select 1 from tableb b where a.... = b....
) and exists (
select 1 from tablec c where a.... = c....
)
/Lennart

Thank you for the suggestion.

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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Dave | last post: by
reply views Thread by Alpenk | last post: by
5 posts views Thread by Harry Broomhall | last post: by
3 posts views Thread by charles | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.