时间戳

首页 > 解决方案 > 如何在特定列的一个查询中更新多行值

mysql - 如何在特定列的一个查询中更新多行值

问题描述

UPDATE job_seekers SET js_email = 
       CASE js_email 
              WHEN "Wbsirota@gmail.com" THEN "bsirota@gmail.com" 
              WHEN "@nautiyalanuj@gmail.com"THEN "nautiyalanuj@gmail.com" 
              WHEN ".agiletestanalyst​@gmail.com" THEN "agiletestanalyst​@gmail.com" 
       END ) WHERE js_email IN ("wbsirota@gmail.com", 
                                "@nautiyalanuj@gmail.com", 
                                ".agiletestanalyst​@gmail.com");

标签： mysql

解决方案

 UPDATE
   job_seekers
 SET
    js_email = CASE WHEN js_email = 'Wbsirota@gmail.com' THEN 
    'bsirota@gmail.com' WHEN js_email = '@nautiyalanuj@gmail.com' THEN 
    'nautiyalanuj@gmail.com' WHEN js_email = '.agiletestanalyst​ 
    @gmail.com' THEN 'agiletestanalyst​@gmail.com' ELSE js_email END
    WHERE
    js_email IN ("wbsirota@gmail.com","@nautiyalanuj@gmail.com",
    ".agiletestanalyst​@gmail.com");

示例如下

   UPDATE
       dbo.TestStudents
   SET
       LASTNAME = CASE WHEN LASTNAME = 'AAA' THEN 'BBB'
       WHEN LASTNAME = 'CCC' THEN 'DDD' 
       WHEN LASTNAME = 'EEE' THEN 'FFF' ELSE LASTNAME END
       WHERE
       LASTNAME IN ('AAA', 'CCC', 'EEE');

推荐阅读