updating a table in tsql with multiple conditions

If I have a table MyTable with columns a,b and c, which are ints. Given that I want to update all 'a's based on the values of b and c.

Update MyTable set a = 2 where b = 1 and c = 1

It's far too late, and I cannot for the life of me see why this statement doesn't work, am I missing something silly?

Edit, woops, forgot 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."

Edit2: That was the exact query I was using (different column names). T开发者_运维问答urns out there was a trigger on the table which was broken. I feel a little silly now, but thanks for the help anyway :)

There's nothing wrong with the statement you posted. The error is elsewhere.

Could you have posted the wrong query? Or perhaps you over-simplified it? A subquery looks something like this:

UPDATE MyTable
SET a = 2 
WHERE b = 1 AND c = (SELECT c FROM MyTable2 WHERE id = 5)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ <--- subquery

An invalid query that could give the error message you get could look like this:

UPDATE MyTable
SET a = 2 
WHERE b = 1 AND c = (SELECT c, d FROM MyTable2 WHERE id = 5)

The second query is invalid because it returns two values but the = operator only allows comparison to a single value.

The solution is to ensure that all subqueries used in equality comparisons only return a single row consisting of a single column.