Problem with update statement

Question

I am trying to update all records in a column so that they start with 'CD' e.g. DCE206 would become CDE206.

UPDATE table
SET column = REPLACE(column1, 'DC', 'CD')
WHERE column1 LIKE 'DC%'

I am using the above update statement however the following error appears

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

Is there anything I can change in the statement to make this happen or do I need to look into using a cursor.

I am using SQL Server 2000.

Answer 1

This seems to work fine on my side, 2005. Is this part of a batch of queries, perhaps another update is causing this message?

Edit: Ran this on SQL 2000, no errors.

create table table1 (id int identity, column1 varchar(10))

insert into table1 values ('DCE1')
insert into table1 values ('DCE2')
insert into table1 values ('DCE3')
insert into table1 values ('DCE4')
insert into table1 values ('DCE5')

UPDATE table1
SET column1 = REPLACE(column1, 'DC', 'CD')
WHERE column1 LIKE 'DC%'

select * from table1 
drop table table1

Answer 2

That statement you posted will not generate that error: it has no sub-query.

Answer 3

You can't possibly be getting this error from that code. The error msut be from some other peice of code.

Do you have anything than runs on update? A trigger?

Answer 4

You seem to have a typo. It should be:

"SET column1 = "

instead of

"SET column = "

(there is a missing '1')

Answer 5

If you're getting the error you indicate from a simple UPDATE, then most likely you have an UPDATE trigger on the table, and the error is coming from the trigger.

However, the code you posted is certainly not the code you are having trouble with, because "table" is a keyword and can't be the name of the table you're updating.