UPDATE newhouse_clicks
SET clicks = CASE id
WHEN 1 THEN 1000
WHEN 2 THEN 2000
WHEN 3 THEN 3000
END
WHERE id IN (1,2,3)
咱们详解一下这条sql语句的意思:
更新newhouse_clicks数据表中的clicks字段,当id=1是设置值为1000,当id=2时设置值为2000,当id=3时设置值为3000
更新多个字段代码:
UPDATE newhouse_clicks
SET clicks = CASE id
WHEN 1 THEN 1000
WHEN 2 THEN 2000
WHEN 3 THEN 3000
END,
type = CASE id
WHEN 1 THEN 1
WHEN 2 THEN 6
WHEN 3 THEN 8
END
WHERE id IN (1,2,3)
这条sql语句的含义就是更新newhouse_clicks数据表中的clicks字段,当id=1是设置值为1000,当id=2时设置值为2000,当id=3时设置值为3000,更新type字段,当id=1时更新为type字段为1,当id=2时更新type字段为6,当id=3时,更新type字段为8。
下面使用php 生成一条sql:
//查询数据库返回的数据格式
$newhouse_clicks = array(
=> 2,
=> 3,
=> 8,
=> 9,
);
$ids = implode(',', array_keys($newhouse_clicks ));
$sql = "UPDATE newhouse_clicks SET clicks = CASE id ";
foreach ( $newhouse_clicks as $key => $value) {
$sql .= sprintf("WHEN %d THEN %d ", $key, $value);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;