Transact-SQL 示例 - 一个UPDATE实现多个数据列的条件更新

BEGIN TRAN
   
   SELECT 1 A, 0 B INTO #tem UNION ALL
   SELECT 1, 1 UNION ALL
   SELECT 0, 1 UNION ALL
   SELECT 1, 0 UNION ALL
   SELECT 0, 0

   --查看演示的数据
   SELECT * FROM #tem

   --把A, B列各自取反,注意如果不是bit类型,会纠结..
   UPDATE #tem SET
      A = ~CAST(A AS bit) ,
      B = ~CAST(B AS bit) 

   --查看更新后的效果
   SELECT * FROM #tem

   --把A, B列各自取反,CASE版
   UPDATE #tem SET
      A = CASE A 
            WHEN 1 THEN 0
            WHEN 0 THEN 1
            ELSE 0
          END,
      B = CASE B
            WHEN 1 THEN 0
            WHEN 0 THEN 1
            ELSE 0
          END

   --查看更新后的效果
   SELECT * FROM #tem

ROLLBACK