TSQL, update col a = 0 set all the values for col a =0 in the table

I am trying to find way to do below in SQL table, where a is bit data type

• if update col a = 0 all the other values for col a in the table should be updated to 开发者_如何学Python0 and
• if update col a = 1 all the other values for col a in the table should be updated to 1.

You can use the INSTEAD OF trigger. It's a Highlander-type trigger, meaning there can only be one of it on a table (for every operation).

CREATE TRIGGER MyTable_SetBitColumn ON MyTable INSTEAD OF UPDATE
AS BEGIN
  UPDATE MyTable
  SET BitColumn = i.BitColumn
  FROM (SELECT TOP 1 * FROM inserted) i
END

Do you mean you want to toggle all values for that column? If so:

UPDATE table
SET BitColumn = 1 - BitColumn

How about

UPDATE mytable SET a=1

or

UPDATE mytable SET a=0

If all the rows in the table will have the same value then don't store this column in this table. Store it in another table with 1 row. You can then cross join onto the single row table when doing your queries.