SQL:批量更新不同字段不同值

2023年8月3日发(作者：)

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' ENDWHERE 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);

$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);

2023年8月3日发(作者：)

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' ENDWHERE 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);

$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);