Help! can't run Update in SQL 2005

I have a table name : sale there have some item about (SN(int,PK),...Invoice(nvarchar)....)

try run a update query in SQL 2005 :

update sale set Invoive = '99999' where SN in('1','2','3')

Get the error msg:

Msg 512, Level 16, State 1, Procedure Update_Cost, 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.

but run in SQL 2000+SP4 is OK!

what's wrong in SQl2005?

this is working for me,

or

just send us the full test data(Table structure + Test data), check is there some trigger there for this table? go for the same statment for other table, i mean update same fileds, or so.

|||

From your description, SN is integer. The single quotes around the integers should be removed.

UPDATE sale SET Invoice = '99999' where SN in (1, 2, 3)

Martin Poon [MVP - SQL Server]

|||

Thanks a lot!

Find the root cause! after check the DB find there have trigger with updat invoice!

thanks again!