copying data from one table to another in a single SQL statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steveg144@gmail.com - 21 Sep 2007 15:08 GMT

We have a table, called COLL. We have another table, named TEST. In
simple terms,
we need to copy values from a column from COLL to a matching record in
TEST. Some
constraints: this must happen in a single SQL statement (in other
words, not embedded in
an app or script) and must work in DB2 v7 on zOS. What we need to do
would be somewhat
simple in V9 or above using SELECT FROM UPDATE, but we're hitting a
wall with V7.
Details:
COLL has these fields:
FK_TEST_ID
FK_PHONE_ID

TEST has these fields:
TEST_ID
FK_PHONE_ID

What we need to do is to copy the FK_PHONE_ID from all records in COLL
into their matching
records in TEST. By 'matching' I mean where TEST's TEST_ID = COLL's
FK_TEST_ID.

In pseudocode (or I should say, pseudo-SQL):

update TEST set FK_PHONE_ID to FK_PHONE_ID from COLL where
COLL:FK_TEST_ID
= TEST.TEST_ID --- do for all rows

Reply to this Message

Serge Rielau - 21 Sep 2007 15:30 GMT

> We have a table, called COLL. We have another table, named TEST. In
> simple terms,
[quoted text clipped - 14 lines]
> TEST_ID
> FK_PHONE_ID

UPDATE TEST
SET FK_PHONE_ID = (SELECT FK_PHONE_ID FROM COLL
WHERE FK_TEST_ID = TEST_ID)
WHERE EXISTS(SELECT 1 FROM COLL WHERE FK_TEST_ID = TEST_ID)

I'm unclear how SELECT FROM UPDATE helps here...
MERGE is generally considered the better match:
MERGE INTO TEST USING COLL
ON FK_TEST_ID = TEST_ID
WHEN MATCHED THEN UPDATE SET FK_PHONE_ID = COLL.FK_PHONE_ID

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Dan van Ginhoven - 25 Sep 2007 21:18 GMT

Hi!

The Merge statement is what you are missing, but is not available until in
V9, alas.

You can try the following

Update TEST A
Set A.FK_PHONE_ID = (Select FK_PHONE_ID from COLL )
where exists (
Select 1 from COLL B
where B.FK_TEST_ID = A. TEST_ID
);

/dg

> We have a table, called COLL. We have another table, named TEST. In
> simple terms,
[quoted text clipped - 25 lines]
> COLL:FK_TEST_ID
> = TEST.TEST_ID --- do for all rows

Reply to this Message

rahul - 26 Sep 2007 14:43 GMT

> Hi!
>
[quoted text clipped - 47 lines]
>
> - Show quoted text -

merge is available in db2 8.2 luw

Reply to this Message