Problem with update statement

No matter what I do I cant get this update statement to work..
UPDATE MedOrder
SET AdditionalRefills = 0
WHERE AdditionalRefills IS NULL
I get this error message:
Server: Msg 512, Level 16, State 1, Procedure MEDORDER_MODIFIED, Line 6
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.
Ive even tried it this way:
UPDATE MedOrder
SET AdditionalRefills = 0
WHERE [med order id ] in
(SELECT [med order id]
FROM medorder
WHERE AdditionalRefills IS NULL)
And this way:
declare @.temp table
(
medid int
)
insert into @.temp select [med order id] from medorder where
Additionalrefills is null
UPDATE MedOrder
SET AdditionalRefills = 0
WHERE [med order id] in
(select medid from @.temp)
All give me the same error message..how can I accomplish this?
thanksThere was a bug in my trigger..that was the problem..|||What you did first looks fine. But it does not have six lines of code
or a subquery. Is there some other stuff in the procedure you are not
showing us?|||The problem is not actually with this update... it looks like you have a
trigger that is "fired" then the row is updated...or when the
AdditionalRefills column is updated.
Check the query that is being executed on the rigger...
"MEDORDER_MODIFIED"
Server: Msg 512, Level 16, State 1, Procedure MEDORDER_MODIFIED, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery
Message posted via http://www.webservertalk.com|||Code looks good from here. Is there something else that could be affecting
it? BTW, you might consider setting a default of 0 on that column.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:B95757DE-593A-40A4-BD57-B2499048903A@.microsoft.com...
> No matter what I do I cant get this update statement to work..
> UPDATE MedOrder
> SET AdditionalRefills = 0
> WHERE AdditionalRefills IS NULL
> I get this error message:
> Server: Msg 512, Level 16, State 1, Procedure MEDORDER_MODIFIED, Line 6
> 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.
> Ive even tried it this way:
> UPDATE MedOrder
> SET AdditionalRefills = 0
> WHERE [med order id ] in
> (SELECT [med order id]
> FROM medorder
> WHERE AdditionalRefills IS NULL)
>
> And this way:
> declare @.temp table
> (
> medid int
> )
> insert into @.temp select [med order id] from medorder where
> Additionalrefills is null
>
> UPDATE MedOrder
> SET AdditionalRefills = 0
> WHERE [med order id] in
> (select medid from @.temp)
> All give me the same error message..how can I accomplish this?
> thanks
>