By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

How to UPDATE with joins

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a

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

P: n/a
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

P: n/a
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

P: n/a
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.