mysql批量更新多个字段不同值

2019独角兽企业重金招聘Python工程师标准>>>

今天做项目需求发现一个比较好用的单条SQL语句mysql批量更新数据库.

CASE  WHEN  THEN  END 

            //还原城市原有排序
            $ids = '';
            $sql = "UPDATE ffan_cities SET asort = CASE cityId ";
            foreach ($showCities as $k => $v) {
                $sql .= sprintf("WHEN %d THEN %d ", $v['cityId'], $v['asort']);
                $ids .= $v['cityId'].',';
            }
            $ids = trim($ids,',');
            $sql .= "END WHERE cityId IN ($ids)";
            $res = $this->execute($sql);

看上面代码,最后一句代码执行SQL语句,前面都是组织好SQL语句准备的.最得出这样一条SQL:

UPDATE ffan_cities

SET asort = CASE cityId 

WHEN 11023 THEN 3

WHEN 11024 THEN 7 

END

WHERE cityId IN (11023,11024)

 这条SQL很好理解,条件当cityId=11023时 asort更新为3,后面的类推,最后where条件 cityId IN 这个组里边为了提高执行效率.

还有个可以批量更新多个字段的语句,大家可以看看.(网上找的例子,其实都差不多)

UPDATE categories 
    SET display_order = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
    END, 
    title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

转载于:https://my.oschina.net/phperchenlong/blog/810486