SQL：批量更新不同字段不同值
1.修改字段信息：
ALTER TABLE `table_name` CHANGE `is_own` `is_self_supply` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0第三⽅供货商 1⾃有供货商';
2.批量更新不同字段不同值
UPDATE `table_name`
SET status = 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);
以php为例，构造这条mysql语句：
$status = array(
1 => 4,
2 => 1,
3 => 2,
4 => 3,
5 => 9,
6 => 5,
7 => 8,
8 => 9批量更新sql语句
);
$ids = implode(',', array_keys($status ));
$sql = "UPDATE `table_name` SET status = CASE id ";
foreach ($status as $id => $status ) {
$sql .= sprintf("WHEN %d THEN %d ", $id, $status );
}
$sql .= "END WHERE id IN ($ids)";
var_dump($sql);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。