update批量更新mysql数据

   2022-07-04 18:14:05

mysql

sql语句：
UPDATE table1
    SET field1 = CASE other_field
        WHEN 1 THEN 'value1'
        WHEN 2 THEN 'value2'
        WHEN 3 THEN 'value3'
        WHEN 4 THEN 'value4'
    END
WHERE id IN (1,2,3,4)

示例：
$segmentInfo = Array
(
    "2022053001" => Array
        (
            [segment_id] => 2022053001
            [priority] => 21
            [auto_refresh] => 1
            [base_bidding] => 1
        ),
    "2022053002" => Array
        (
            [segment_id] => 2022053002
            [priority] => 22
            [auto_refresh] => 1
            [base_bidding] => 1
        )
)
$checkParams = ['segment_id','priority','auto_refresh','base_bidding'];

public function editAdvSegment($segmentInfo,$checkParams) {
    $segmentIds = implode("','",array_keys($segmentInfo));
    $sql = 'UPDATE '.self::$t1.' SET ';
    foreach($checkParams as $field){
        $sql .= $field.' = (CASE segment_id ';
        foreach($segmentInfo as $k => $v){
            $sql .= " WHEN '{$k}' THEN '{$v[$field]}' ";
        }
        $sql .= ' END),';
    }
    $sql = rtrim($sql, ",");
    $sql .= "WHERE segment_id IN ('{$segmentIds}')";
    //执行sql
}