Select statement in Update query
-
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.
-
Your problem is that the sub query (Select statement) is returning more than one value for 1 or more DIAGNOSTICPROCIDs and SQL server is cannot decide which one to use.
You could force it to use the first value by adding TOP 1 to your SELECT statement (or Max() etc) but you are probably better trying to run the SELECT statement seperately and trying to understand why the duplicate occur.
-
Try adding DISTINCT keyword first - SET SoDiagnosisID = (select DISTINCT A.SoDiagnosisID ......) - maybe your query just returns the same value several times. SQLS does not check, whether the values are different or not; if you use (SELECT...) in the SET command, it simply must return only 1 value for every row.
If that does not help, follow Daniel's advice and find out why the duplicates occur and how to get rid of them.
HTH, Vladan
-
UPDATE c
SET c.SoDiagnosisID = a.SoDiagnosisID
from inv_schmerge c
inner join DiagnosticProc_DS b on
b.DiagnosticProcProcID = c.Diagnosticprocid
inner join Diagnosis_ds a on
a.DiagnosisName=b.DiagnosisName
where c.sodiagnosisid is null
From this example you can see that I am updating based on the alias. This allows you to update a table used in the from clause.
This will be much faster than doing the sub-select. The only issue that I see is that because you are joining on DiagnosisName, you will still have a problem unless your DiagnosisName is unique in the Diagnosis_DS table.
You may want to add DiagnosisCategory or replace DiagnosisName with DiagnosisID in DiagnosisProc_DS.
If you add DiagnosisCategory the query would change to this:
UPDATE c
SET c.SoDiagnosisID = a.SoDiagnosisID
from inv_schmerge c
inner join DiagnosticProc_DS b on
b.DiagnosticProcProcID = c.Diagnosticprocid
inner join Diagnosis_ds a on
a.DiagnosisName=b.DiagnosisName and
a.DiagnosisCategory= b.DiagnosisCategory
where c.sodiagnosisid is null
and your unique index would be on DiagnosisName and DiagnosisCategory on the Diagnosis_DS table.
I hope this helps.
Damien
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply