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

今天做项目需求发现一个比较好用的单条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)