Select statement in update - 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 2000 Forums

SQL Server Development (2000)

Select statement in update

Author

Topic

shilpa
Starting Member

2 Posts

Posted - 2005-11-02 : 03:05:00

I was trying to replace null values of the DiagnosisIDs in Inv_SchMerge table with the DiagnosisIDs by refering to the DiagnosticProcedures done for that diagnosis.

UPDATE Inv_SchMerge
SET SoDiagnosisID = (
select A.SoDiagnosisID
from Diagnosis_DS A,DiagnosticProc_DS B
WHERE A.DIAGNOSISNAME=B.DIAGNOSISNAME
AND B.DIAGNOSTICPROCID=Inv_SchMerge.DIAGNOSTICPROCID

WHERE Inv_SchMerge.SODIAGNOSISID IS NULL

SQL Exception:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Inv_SchMerge table has null values for DiagnosisIDs.

Inv_SchMerge table has ScheduleID,PatientID,SoDiagnosisID,InvoiceID,ClinicID,DiagnosticProcID

Diagnosis Table has:

SoDiagnosisID,DiagnosisName,DiagnosisCategory,DiagnosisType

DiagnosticProc_DS has:

DiagnosticProcID,DiagnosisName,DiagnosticProc

Any help will be greatly appreciated.

ThankYou.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-02 : 03:15:29

Try this

Commit Transaction


UPDATE I SET I.SoDiagnosisID = T.SoDiagnosisID 
	from Inv_SchMerge I inner join 
	(select A.SoDiagnosisID 
	from Diagnosis_DS A,DiagnosticProc_DS B
	WHERE A.DIAGNOSISNAME=B.DIAGNOSISNAME
	) T on 
I.DIAGNOSTICPROCID=T.DIAGNOSTICPROCID
WHERE I.SODIAGNOSISID IS NULL

Now run this select query

Select * from Inv_SchMerge

If you are satisfied with the results then run

Commit Transaction

Otherwise

Rollback Transaction

Madhivanan

Failing to plan is Planning to fail

shilpa
Starting Member

2 Posts

Posted - 2005-11-02 : 15:04:27

Thank you so much that code worked....It helped me....
Thankyou again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 00:32:57

Well. In Books On Line, SQL Server help file look for UPDATE

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources