Solved

UPDATE-SELECT WITH MULTIPLE COLUMNS

Posted on 2007-12-04
4
13,943 Views
Last Modified: 2012-06-21
I want to copy the data of two columns from the record with an Enroll_ID of 1234 and paste it into the same columns for the record with an PRGM_ID of 2222.  This first query here is only with one column, and it works!!  But how do I get it to work with two columns? This is all in the same table...

UPDATE TABLEA
SET OFFER_NUM = (SELECT OFFER_NUM FROM TABLEA WHERE ENROLL_ID = 1234)
WHERE PRGM_ID = 2222

The above statement works!  But here's what I'm trying to do below (the same thing, but with multiple columns), and it's not working...
UPDATE TABLEA
SET (OFFER_NUM, PHONE_YN) = (SELECT OFFER_NUM, PHONE_YN FROM TABLEA WHERE ENROLL_ID = 1234)
WHERE PRGM = 2222

I get the error, "Incorrect syntax near '('" and "Incorrect syntax near the keyword 'WHERE'"  Please help, this is driving me up the wall!!
               
0
Comment
Question by:redmission
  • 2
4 Comments
 
LVL 22

Expert Comment

by:danaseaman
ID: 20407499
Typo?
WHERE PRGM = 2222 should be WHERE PRGM_ID = 2222

UPDATE TABLEA
SET (OFFER_NUM, PHONE_YN) = (SELECT OFFER_NUM, PHONE_YN FROM TABLEA WHERE ENROLL_ID = 1234)
WHERE PRGM_ID = 2222
0
 

Author Comment

by:redmission
ID: 20407612
Sorry, I did type that wrong here...but it's not wrong in my code.  It actually is PRGM_ID.   That isn't the problem. :)
0
 
LVL 22

Accepted Solution

by:
dportas earned 500 total points
ID: 20407684
In standard SQL (2003) you could use a MERGE statement:

MERGE INTO TABLEA A
USING TABLEA B
 ON A.PRGM = 2222 AND B.ENROLL_ID = 1234
WHEN MATCHED THEN
UPDATE SET OFFER_NUM = B.OFFER_NUM,
 PHONE_YN = B.PHONE_YN;

This is supported by SQL Server 2008 but not by earlier versions.

SQL Server 2000 and 2005 have an alternative non-standard "UPDATE... FROM" syntax. The only problem is that it fails to warn you with an error when potentially ambiguous join criteria are used. ie. joins that return more than one possible value for a row being updated. In that case the UPDATE statement given below will update some random value into the columns being update, which is not generally good news!

As long as there is only one row where ENROLL_ID=1234 then the UPDATE below is "safe". And if there was more than one 1234 row then your first UPDATE statement would also have failed. Personally however, I can't wait until MERGE comes along in 2008 and we can consign this lousy "UPDATE FROM" syntax to the trash where it belongs!

UPDATE A
 SET OFFER_NUM = B.OFFER_NUM,
 PHONE_YN = B.PHONE_YN
FROM TABLEA A
JOIN TABLEA B
 ON A.PRGM = 2222 AND B.ENROLL_ID = 1234;
0
 

Author Closing Comment

by:redmission
ID: 31412721
dportas, your solution saved the day!  I just graduated in May with a CIS degree, so I still have a ways to go before become an "expert" at SQL! :)  I did exactly what your solution said...except for adding another column to join on.  It works!

I didn't have to worry about the "safety" of it, because the ENROLL_ID I used is the auto number primary key for the table.  Thanks again.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now