468,257 Members | 1,460 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

sql update from stage tables with two column keys db2 V8

Given table a: col1 col2 name1 name2 where col1 and col2 make up the
unique key.
Given a stage table b: col1 col2 name1 name2. (again unique key same
as a)

table a has 34M rows
table b has 2 million rows

i want to update table a's name1 and name2 with the values from table
b
where a.col1 = b.col1 and a.col2 = b.col2.

I don't think this can be done within a sql update statement so I am
writing a program to do a cursor update; However I would think the
db2 engine would be more efficient.
Can it be done?

Thanks,
Mike
Nov 12 '05 #1
2 8178
Mike wrote:
I don't think this can be done within a sql update statement so I am
writing a program to do a cursor update; However I would think the
db2 engine would be more efficient.
Can it be done?

Of course :-)
The classic (SQL 92):
UPDATE a
SET (name1, name2) = (SELECT name1, name2 FROM b
WHERE a.col1 = b.col1 and a.col2 = b.col2)
WHERE EXISTS(SELECT 1 FROM b
WHERE a.col1 = b.col1 and a.col2 = b.col2)
Presuming (col1,colb) have a unique index on both A and B DB2 merge the
two queries on B.

The modern (SQL 4):
MERGE INTO a
USING b
ON a.col1 = b.col1 and a.col2 = b.col2
WHEN MATCHED THEN UPDATE SET (name1, name2) = (b.name1, b.name2)

Cheers
Serge
Nov 12 '05 #2
Serge

Thanks for your excellent examples...!
Mike
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Jan van Veldhuizen | last post: by
17 posts views Thread by kalamos | last post: by
16 posts views Thread by robert | last post: by
3 posts views Thread by Michel Esber | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.