Update Query Failing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Query Failing

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-03 : 08:43:46

UPDATE EMRGUARANTORSINFO
SET DATE_OF_CREATION=
C.DATE_OF_CREATION FROM EMRPATIENTINSURANCE EPI,
(SELECT EPI.DATE_OF_CREATION,EPI.GUARANTOR_ID,EG.DATE_OF_CREATION,EG.GUARANTOR_ID FROM EMRGUARANTORSINFO EG INNER JOIN
EMRPATIENTINSURANCE EPI ON EPI.GUARANTOR_ID=EG.GUARANTOR_ID AND EPI.PATIENT_ID=EG.PATIENT_ID) C
WHERE C.GUARANTOR_ID=EG.GUARANTOR_ID
GO

error:

Msg 8156, Level 16, State 1, Line 1
The column 'DATE_OF_CREATION' was specified multiple times for 'C'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "EG.GUARANTOR_ID" could not be bound.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-03 : 09:49:19

You have EPI.DATE_OF_CREATION and EG.DATE_OF_CREATION in your sub select. SQL doesn't know which one you want. Give at least one of them an alias. E.g, EPI.DATE_OF_CREATION as 'CreationDate'

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 09:53:39

also why do you need to use two instances of EMRPATIENTINSURANCE table here? I think you need only a single instance which you join with EMRGUARANTORSINFO like below


UPDATE EG
SET EG.DATE_OF_CREATION=EPI.DATE_OF_CREATION 
FROM EMRGUARANTORSINFO EG INNER JOIN 
EMRPATIENTINSURANCE EPI 
ON EPI.GUARANTOR_ID=EG.GUARANTOR_ID 
AND EPI.PATIENT_ID=EG.PATIENT_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources