S Chrom S Chrom - 1 year ago 66
SQL Question

Update query in TSQL throwing error

I am trying to run an update query, seems simple enough I am updating all the fields InitialCharge to a value of '21.61' everywhere that the current InitialCharge = '20.72', I also have some other qualifying fields. I am getting the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Here is my query, I tried using "IN" and "Exists" but neither work:

update [dbo].[tTransactionHistory]
set InitialCharge = '21.61'
Where exists(

select InitialCharge from [dbo].[tTransactionHistory]
where ProcedureID = '-747422922' and InitialCharge = ('20.72') and (DateOfService >= '09/01/2014' and DateOfService < '10/01/2014')


I even tried a simple update query and I get the error, Ive never run into this issue trying to just update several fields

update [dbo].[tTransactionHistory]
set InitialCharge = '21.61'
where InitialCharge = '20.72'

Error Screen Shot

Answer Source

I'm confused by why you need an EXISTs subquery at all...

UPDATE [dbo].[tTransactionHistory]
SET InitialCharge = '21.61'
    ProcedureID = '-747422922' AND
    InitialCharge = '20.72' AND
    (DateOfService >= '2014/09/01' and DateOfService < '2014/10/01')

And are you sure the table columns are strings??

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download