mysql - 如何向 mySQL 更新查询添加条件语句？

Question

我有以下 mySQL 更新查询。如何添加条件 WHEN 语句，其中表示 if Status =“PS”更改为“PE”，if Status =“DS”更改为“DE”

我不确定如何用正确的语法编写它。

 UPDATE Equipment SET delivery_time = "5 pm",
 stop_sequence = "Stop 2" 
WHERE (status = "Fixed" or status = "Broken")
 AND customer = "Mary" AND driver = "Pete"

Answer 1

使用案例

 UPDATE Equipment SET delivery_time = '5 pm',
 stop_sequence = 'Stop 2',
 status = case  status when 'PS' then 'PE' when 'DS' Then 'DE'
          else status end
 WHERE status in ('Fixed' 'Broken')
 AND customer = 'Mary' AND driver = 'Pete'