Miggy


Hi, I'm trying to update a table with subqueries but sometimes I get msg 512 e.g. 'update 1' below works, 'update 2' doesn't. View2 appears OK and the only difference I can see is Haematuria is nVarchar(25) and PosturalHypotension is a float. Both fields Allow Nulls

Update 1:

UPDATE Table1
SET Haematuria
= (SELECT Haematuria
FROM dbo.View1
WHERE Table1.PatientCode =
View1.PatientCode)

Update 2:

UPDATE Table 1
SET PosturalHypotension
= (SELECT PosturalHypotension
FROM View2
WHERE Table1.PatientCode =
View2.PatientCode)




Re: Error Message 512

DaleJ


The problem is that the subquery

(SELECT PosturalHypotension
FROM View2
WHERE Table1.PatientCode =
View2.PatientCode)

is returning more than a single result.

You'll need to refine this query.







Re: Error Message 512

Miggy

Sorry for being dumb but subquery 1 returns 12 rows, subquery 2 returns 27 rows - or is this not the same thing






Re: Error Message 512

DaleJ

It's an issue of how many rows it returns for a given PatientCode.

If Update1 is working, I'd venture to guess that that query only returns one value for a given patient; while Update2 returns multiples in (some) instances.

Maybe you need a further qualification to limit the subquery and/or manage the update.