Mysql根据字段a的不同值，更新字段b为不同值 - 《MySql》

UPDATE cp_system_param SET `value`=
CASE 
WHEN cp_system_param.`code`='system_name' THEN 'aa' 
WHEN cp_system_param.`code`='system_title' THEN 'bb' 
WHEN cp_system_param.`code`='web_flg' THEN 'cc' ELSE `value` END

注意：这里SQL如果不加 ELSE END会导致别的字段值为空。

update `dd`
set xzjd=
case
WHEN xzjd='330683001' then '剡湖街道'
WHEN xzjd='330683003' then '鹿山街道'
WHEN xzjd='330683004' then '浦口街道'
WHEN xzjd='330683100' then '甘霖镇'
WHEN xzjd='330683101' then '长乐镇'
WHEN xzjd='330683102' then '崇仁镇'
WHEN xzjd='330683103' then '黄泽镇'
WHEN xzjd='330683104' then '三界镇'
WHEN xzjd='330683107' then '仙岩镇'
WHEN xzjd='330683110' then '下王镇'
WHEN xzjd='330683111' then '石璜镇'
WHEN xzjd='330683112' then '谷来镇'
WHEN xzjd='330683113' then '金庭镇'
WHEN xzjd='330683206' then '贵门乡'
WHEN xzjd='330683002' then '三江街道'

else xzjd end
WHERE  left(xzjd, 1)<>'330683';

update `biz_06053089_sz_jtczryjcjkxx` set xzjd=
case
WHEN SBSSDW='嵊州新昌动车站' THEN '330683002' 
WHEN SBSSDW='嵊州新昌站' THEN '330683002' 
WHEN SBSSDW='嵊州北动车站' THEN '330683104' 
WHEN SBSSDW='嵊州北站' THEN '330683104' 
ELSE `xzjd` END
where xzjd is null 

update `biz_06053089_sz_jtczryjcjkxx` set sq=
case
WHEN SBSSDW='嵊州新昌动车站' THEN '茶坊居委会' 
WHEN SBSSDW='嵊州新昌站' THEN '茶坊居委会' 
WHEN SBSSDW='嵊州北动车站' THEN '长桥村委会' 
WHEN SBSSDW='嵊州北站' THEN '长桥村委会' 
ELSE `sq` END
where sq is null

SELECT
    `biz_06053083_sz_csryjcjkxx`.`ID` AS `ID`,
    `biz_06053083_sz_csryjcjkxx`.`GSID` AS `GSID`,
    ( CASE `biz_06053083_sz_csryjcjkxx`.`GSMC` WHEN '场所码' THEN '非线' ELSE `biz_06053083_sz_csryjcjkxx`.`GSMC` END ) AS `GSMC`,
    `biz_06053083_sz_csryjcjkxx`.`XM` AS `XM`,
    `biz_06053083_sz_csryjcjkxx`.`SFZ` AS `SFZ`,
    `biz_06053083_sz_csryjcjkxx`.`JCLX` AS `JCLX`,
    `biz_06053083_sz_csryjcjkxx`.`JCSJ` AS `JCSJ`,
    `biz_06053083_sz_csryjcjkxx`.`YMJZQK` AS `YMJZQK`,
    `biz_06053083_sz_csryjcjkxx`.`TW` AS `TW`,
    `biz_06053083_sz_csryjcjkxx`.`HSSJ` AS `HSSJ`,
    `biz_06053083_sz_csryjcjkxx`.`SBDZ` AS `SBDZ`,
    `biz_06053083_sz_csryjcjkxx`.`SSQX` AS `SSQX`,
    `biz_06053083_sz_csryjcjkxx`.`SSSQ` AS `SSSQ`,
    `biz_06053083_sz_csryjcjkxx`.`SBXLH` AS `SBXLH`,
    `biz_06053083_sz_csryjcjkxx`.`SBSSDW` AS `SBSSDW`,
    `biz_06053083_sz_csryjcjkxx`.`JD` AS `JD`,
    `biz_06053083_sz_csryjcjkxx`.`WD` AS `WD`,
    `biz_06053083_sz_csryjcjkxx`.`cslx` AS `DWLX`,
    `biz_06053083_sz_csryjcjkxx`.`tongID` AS `tongID`,
    `biz_06053083_sz_csryjcjkxx`.`op` AS `op`,
    (
CASE
    `biz_06053083_sz_csryjcjkxx`.`JKM` 
    WHEN '无码' THEN
    '0' 
    WHEN '绿码' THEN
    '1' 
    WHEN '红码' THEN
    '2' 
    WHEN '黄码' THEN
    '3' 
    WHEN '0' THEN
    '0' 
    WHEN '1' THEN
    '1' 
    WHEN '2' THEN
    '2' 
    WHEN '3' THEN
    '3' ELSE '0' 
END 
    ) AS `JKM`,
    ( CASE `biz_06053083_sz_csryjcjkxx`.`XC` WHEN '1' THEN '1' WHEN '2' THEN '2' WHEN '3' THEN '3' ELSE '3' END ) AS `XC`,
    (
CASE

    WHEN ( `biz_06053083_sz_csryjcjkxx`.`SJH` = '' ) THEN
    '00000000000' 
    WHEN isnull( `biz_06053083_sz_csryjcjkxx`.`SJH` ) THEN
    '00000000000' 
    WHEN ( `biz_06053083_sz_csryjcjkxx`.`SJH` = '无' ) THEN
    '00000000000' ELSE `biz_06053083_sz_csryjcjkxx`.`SJH` 
END 
    ) AS `SJH`,
    ( CASE `biz_06053083_sz_csryjcjkxx`.`GSMC` WHEN '场所码' THEN '嵊州市场所码' ELSE concat( `biz_06053083_sz_csryjcjkxx`.`SBSSDW`, '进站机' ) END ) AS `SBMC`,
    (
    CASE
            `biz_06053083_sz_csryjcjkxx`.`HSJG` 
            WHEN '阴性' THEN
            '1' 
            WHEN '阳性' THEN
            '2' 
            WHEN '无结果' THEN
            '3' 
            WHEN '1' THEN
            '1' 
            WHEN '2' THEN
            '2' 
            WHEN '3' THEN
            '3' ELSE '3' 
        END 
        ) AS `HSJG`,
        (
        CASE
                `biz_06053083_sz_csryjcjkxx`.`SSJD` 
                WHEN '330683001' THEN
                '剡湖街道' 
                WHEN '330683003' THEN
                '鹿山街道' 
                WHEN '330683004' THEN
                '浦口街道' 
                WHEN '330683100' THEN
                '甘霖镇' 
                WHEN '330683101' THEN
                '长乐镇' 
                WHEN '330683102' THEN
                '崇仁镇' 
                WHEN '330683103' THEN
                '黄泽镇' 
                WHEN '330683104' THEN
                '三界镇' 
                WHEN '330683107' THEN
                '仙岩镇' 
                WHEN '330683110' THEN
                '下王镇' 
                WHEN '330683111' THEN
                '石璜镇' 
                WHEN '330683112' THEN
                '谷来镇' 
                WHEN '330683113' THEN
                '金庭镇' 
                WHEN '330683206' THEN
                '贵门乡' 
                WHEN '330683002' THEN
                '三江街道' 
                WHEN '剡湖街道' THEN
                '剡湖街道' 
                WHEN '鹿山街道' THEN
                '鹿山街道' 
                WHEN '浦口街道' THEN
                '浦口街道' 
                WHEN '甘霖镇' THEN
                '甘霖镇' 
                WHEN '长乐镇' THEN
                '长乐镇' 
                WHEN '崇仁镇' THEN
                '崇仁镇' 
                WHEN '黄泽镇' THEN
                '黄泽镇' 
                WHEN '三界镇' THEN
                '三界镇' 
                WHEN '仙岩镇' THEN
                '仙岩镇' 
                WHEN '下王镇' THEN
                '下王镇' 
                WHEN '石璜镇' THEN
                '石璜镇' 
                WHEN '谷来镇' THEN
                '谷来镇' 
                WHEN '金庭镇' THEN
                '金庭镇' 
                WHEN '贵门乡' THEN
                '贵门乡' 
                WHEN '三江街道' THEN
                '三江街道' ELSE `biz_06053083_sz_csryjcjkxx`.`SSJD` 
            END 
            ) AS `SSJD` 
        FROM
            `biz_06053083_sz_csryjcjkxx` 
    WHERE
( ( `biz_06053083_sz_csryjcjkxx`.`XM` IS NOT NULL ) AND ( `biz_06053083_sz_csryjcjkxx`.`SFZ` IS NOT NULL ) )
and tongID > ?  ORDER BY tongID LIMIT 0, 100000