CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user1653059
user1653059

Reputation: 51

Update a column where it is NULL

I have a table Person that contains a column isStaff (bit), that allows NULL values.

The purpose is that this field must contains values 0/1; however, due to lack of application handling of user input in the first place, there are multiple records with the NULL value in this column.

If I try to execute this query:

UPDATE Person
SET isStaff = 0
WHERE isStaff IS NULL

I get 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.

Any idea how to resolve this problem?

Upvotes: 3

Views: 451

Answers (1)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32760

If that is your query exactly as posted, then I'm inclined to believe you have a trigger on the Person table you're not even aware of.

I'm also inclined to believe it's doing something along the lines of:

UPDATE SomeOtherTable
SET SomeColumn = 1
WHERE SomeOtherTable.ID = inserted.ID

With triggers, the inserted table contains all records updated, not just a single record, and you would want something like this:

UPDATE SomeOtherTable
SET SomeColumn = 1
WHERE ID IN (SELECT ID FROM inserted)

Of course, this is all speculative until you can confirm whether or not there is a trigger on the table, and if there is, what the trigger is even doing.

Upvotes: 5

Related Questions