Solved

UPDATE-SELECT WITH MULTIPLE COLUMNS

Posted on 2007-12-04
4
13,944 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

867 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

20 Experts available now in Live!

Get 1:1 Help Now