473,890 Members | 1,416 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
3 14884
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_I D

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 21 '07 #2
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*******@gmai l.comwrote in message
news:11******** **************@ g4g2000hsf.goog legroups.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
On Sep 25, 9:18 pm, "Dan van Ginhoven" <danfa...@hotma il.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...@gmai l.comwrote in message

news:11******** **************@ g4g2000hsf.goog legroups.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
24684
by: Bryan Guilliams | last post by:
I'm trying to come up with an elegant, simple way to compare two consecutive values from the same table. For instance: SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESC That gives me the two latest values. I want to test the rate of change of these values. If the top row is a 50% increase over the row below it, I'll execute some special logic.
2
1441
by: William Cleveland | last post by:
I'm working on a system right now where I have a database (two, actually, but one is discarded halfway through), but it's created and used as part of a process (reporting), rather than as the actual production data repository. I may be keeping the database permanantly, but it would be completely read-only; once the process is complete, the database will not change again. This has me wanting to do a few things that are rather foreign to...
13
10457
by: dawatson833 | last post by:
I have several stored procedures with parameters that are defined with user defined data types. The time it takes to run the procedures can take 10 - 50 seconds depending on the procedure. If I change the parameter data types to the actual data type such as varchar(10), etc., the stored procedure takes less that a second to return records. The user defined types are mostly varchar, but some others such as int. They are all input type...
5
3526
by: Lyn | last post by:
I am trying to copy selected fields from one table to another, by reading the source table record into a recordset (ADO) and then copying the relevant fields to an SQL statement "INSERT INTO...". The numeric and text fields copy without a problem, but it all falls in a heap when I try to copy a picture object field (data type OLE Object in both tables). I seem to be missing something ??? Reduced to its simplest form, this is the code...
2
2454
by: Abhishek Srivastava | last post by:
Hello All, Suppose if I have a SQL query like select p.ID, p.NAME, p.UNIT_PRICE, o.QUANTITY from PRODUCT p ORDERS o where p.ID = X AND P.ID = O.ID Here one product can have many orders. Essentially Product is the master table and Orders is the details table.
20
37927
by: TC | last post by:
I need an automated procedure to copy data from an Access table to a SQL Server table. Speed is important. What is the recommended technique? I can export the data from Access, copy it via FTP, then import it into SQL Server. I've tried that, and the speed is acceptable. It is an ugly solution, however, and I expect to find a better one -- preferably a solution better integrated with the Access RDBMS. I've tried using an ODBC...
2
13759
by: Bob | last post by:
I'm running sql server ver 7.0 SP4. I have an access project (.adp) that runs a view which is nothing more than a select statement. Access locks up solid when I try to run this query - with NO error messages what-so-ever. If I sign onto the server, and run the view from enterprise manager - I get: "the data provider or other service returned an E_FAIL status" !?!?! There is NO ERROR number, or further explanation of the message!!
9
3067
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped. Below you will find the code I've written and the error that results. I'm hoping that someone can give me some direction as to what syntax or parameter is missing from the code that is expected by VBA. Overview: I'm trying to copy calculated...
4
1550
by: ray well | last post by:
i need to copy in code a table from a legacy dbase III file into an access mdb file. both source and destination tables have the same table names, field names, field types, & field sizes. the access table has an index attached to it, and starts with a empty table. i want to avoid having to create & copy row by row from the source to the destination table
0
9979
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11234
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10829
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10925
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9640
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5854
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6058
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3282
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.