Updating text fields

[ChrisL]

I am trying to update text fields where the field only has the value
"closed." So a field that has closed and anything else will not be
updated.
update dbo.Hist
set dbo.Hist.Notes = ''
FROM dbo.Hist
WHERE (Notes LIKE 'closed_')

update dbo.Hist
set dbo.Hist.Notes = ''
FROM dbo.Hist
WHERE (Notes LIKE 'closed%') and len(convert
(varchar(7000),notes)) = 7

Neither code works and i get this error: "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."

What's another way to make the update?

[Erland Sommarskog]

[posted and mailed, please reply in news]

ChrisL (ch***********@gmail.com) writes:

I am trying to update text fields where the field only has the value
"closed." So a field that has closed and anything else will not be
updated.
update dbo.Hist
set dbo.Hist.Notes = ''
FROM dbo.Hist
WHERE (Notes LIKE 'closed_')

update dbo.Hist
set dbo.Hist.Notes = ''
FROM dbo.Hist
WHERE (Notes LIKE 'closed%') and len(convert
(varchar(7000),notes)) = 7

Neither code works and i get this error: "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."

Since your UPDATE query cannot result in that error message, my
conclusion is that the table has a poorly written trigger, which assumes
that only one row at a time gets updated. You should probably locate
and fix that trigger.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp