December 10, 2007 at 7:18 am
I'm trying to do a quick fix of some current data with some legacy data. Basically I want to update my current data by setting the SSN equal to the matching record in the old data. I thought this query would do it, but it's not working. Can anybody tell me wheat I'm doing wrong? Thanks!
UPDATE CIS_IVR_Master_200712100600 a
SET a.BI_SSN=(SELECT b.BI_SSN FROM CIS_IVR_Master_200712080600 b WHERE b.BI_ACCT = a.BI_ACCT)
December 10, 2007 at 7:22 am
UPDATE a
SET
a.BI_SSN = b.BI_SSN
FROM CIS_IVR_Master_200712100600 a
INNER JOIN CIS_IVR_Master_200712080600 b
ON b.BI_ACCT = a.BI_ACCT
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 10, 2007 at 7:23 am
Thanks. The above actually worked for me when I removed the a. Can you not set aliases like that in an update?
UPDATE CIS_IVR_Master_200712100600
SET CIS_IVR_Master_200712100600.BI_SSN = (SELECT b.BI_SSN FROM CIS_IVR_Master_200712080600 b WHERE b.BI_ACCT = CIS_IVR_Master_200712100600.BI_ACCT)
December 10, 2007 at 7:26 am
No, you can not define the alias in the UPDATE section, you need it defined in the FROM part of the statement.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 10, 2007 at 7:31 am
Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply