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

[steveg144]

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

[Serge Rielau]

st*******@gmail.com wrote:

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

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dan van Ginhoven]

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

<st*******@gmail.comwrote in message
news:11**********************@g4g2000hsf.googlegro ups.com...

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

[rahul]

On Sep 25, 9:18 pm, "Dan van Ginhoven" <[e-mail]:

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

<[e-mail] in message

news:11**********************@g4g2000hsf.googlegro ups.com...

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- Hide quoted text -

- Show quoted text -

merge is available in db2 8.2 luw