执行一次SQL,UPDATE多行记录

一般情况下更新一条记录

UPDATE TABLE SET `FIELD` ='value' WHERE id ='$id';

如果多N条记录需要更新，那就必须是

Source code

foreach ($sql as $id => $ordinal) {
....
UPDATE TABLE SET `FIELD` ='value' WHERE id ='$id';
 
.....
}

更好的解决方案

Source code

UPDATE TABLE
    SET `FIELD` = CASE `ID`
        WHEN ID1 THEN 'value'
        WHEN ID2 THEN 'value'
        WHEN ID3 THEN 'value'
    END
WHERE id IN (ID1,ID2,ID3)

如果你需要更新一行记录的多个字段，可以用以下SQL语句：

Source code

UPDATE Mate
    SET MateKey = CASE id
        WHEN ID1 THEN 3
        WHEN ID2 THEN 4
        WHEN ID3 THEN 5
    END,
    title = CASE id
        WHEN ID1 THEN 'New Title 1'
        WHEN ID2 THEN 'New Title 2'
        WHEN ID3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

按照语法，PHP的示例

Source code

$display_order = array(
ID1 => NEW VALUE,
ID2 => NEW VALUE,
ID3 => NEW VALUE,
ID4 => NEW VALUE,
ID5 => NEW VALUE,
ID6 => NEW VALUE,
ID7 => NEW VALUE,
ID8 => NEW VALUE
);
$ids = implode(',', $ids);
$sql = "UPDATE `Postmeta` SET `mate_value` = CASE `mate_key` ";
 
foreach ($mate_set as $key => $ordinal) {
$sql .= sprintf("WHEN '%s' THEN '%s' ", $key, $ordinal);  // 拼接SQL语句
}
 
$sql .= "END WHERE `post_id` = '$id' AND `mate_key` IN ($ids)";
 
$model->quert($sql);