同一字段根据不同条件更新的sql语句的写法

[lensh]

同一字段根据不同条件更新的sql语句的写法

update test    
set 字段1=case 
when 条件1 then 值1    
when 条件2 then 值2    
end  

示例：

UPDATE group_user SET unread = CASE 
WHEN is_enter =1 THEN 0 
WHEN is_enter =0 THEN unread +1 END 
WHERE group_id =6

上面这条SQl语句的意思就是，当is_enter为1时，就将unread字段置为0，否则自增1。

when，then也可用于SQL条件判断语句：
第一种：

SELECT
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END AS "Price Category",
CONVERT(varchar(20), title) AS "Shortened Title"
FROM pubs.dbo.titles
ORDER BY price

第二种：

SELECT au_fname, au_lname,
   CASE state
      WHEN 'CA' THEN 'California'
      WHEN 'KS' THEN 'Kansas'
      WHEN 'TN' THEN 'Tennessee'
      WHEN 'OR' THEN 'Oregon'
      WHEN 'MI' THEN 'Michigan'
      WHEN 'IN' THEN 'Indiana'
      WHEN 'MD' THEN 'Maryland'
      WHEN 'UT' THEN 'Utah'
        END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname