473,322 Members | 1,714 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 1400

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
2
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an...
0
by: Alpenk | last post by:
I'm trying to update an empty field in a table using the update statment. The table has a number of joins and the update statement comes up with an error when I enter the main table to update. should...
5
by: Harry Broomhall | last post by:
I wonder if anybody could give me a few pointers on a problem I face. I need to do an UPDATE on table A, from an effective left outer join on A and another table B. (This is trying to perform a...
6
by: Jeff Kowalczyk | last post by:
I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern is a form that will work for a given orderid,...
3
by: charles | last post by:
I've got maybe a stupid question, concerning the difference between a simple select and a select for update ( otherwise identical ). The plan for such selects - in a DPF enviropnment - are...
2
by: joeyrhyulz | last post by:
Hi, I'm trying to make a very simple update statement (in Oracle) in jet sql that seems much more difficult than it should be. The root of my problem is that I'm trying to update a field on a...
5
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the...
8
geolemon
by: geolemon | last post by:
I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now. Arg. I used to be a DBA in large DB2 and SQL...
3
by: modernshoggoth | last post by:
G'day all, I'm trying to update a single table (containing plans for flights) with information from the same and other tables (containing info for organisations and locations). tblFlightPlans...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.