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

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

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

Sep 21 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Sep 21 '07 #2

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

Sep 25 '07 #3

P: n/a
On Sep 25, 9:18 pm, "Dan van Ginhoven" <danfa...@hotmail.comwrote:
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

<steveg...@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- Hide quoted text -

- Show quoted text -
merge is available in db2 8.2 luw

Sep 26 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.