批量更新 （ mysql ）

SQL语句：

UPDATE `database` SET `id` = CASE `id` 
WHEN '1' THEN '1' 
WHEN '2' THEN '2' 
WHEN '3' THEN '3' 
END,`sort` = CASE `id` 
WHEN '1' THEN '1' 
WHEN '2' THEN '3' 
WHEN '3' THEN '5' 
END WHERE `id` IN ('1','2','3')

现在可用实例语句：

update mvno_sim set rent = case id 
when 1 then 100 when 14 then 100 end,
rated_flow = case id 
when 1 then 200 when 14 then 200 end, 
over_price = case id 
when 1 then 300 when 14 then 300 end 
where id in (1,14)

php语句

    public function updateAll($tableName, $ids, $data)
    {
        $sql = '';
        $table = 'update ' . $tableName . ' set ';
        $param = array_keys($data);
        foreach ($param as $key) {
            if ($data[$key] != '') {
                $set = ' ' . $key . ' = case id';
                $where = '';
                foreach ($ids as $item) {
                    $where .= ' when ' . $item . ' then ' . $data[$key];
                }
                $sql .= $set . $where . ' end ,';
            }
        }
        if ($sql) {
            $sql = substr($sql, 0, strrpos($sql, ","));
            $finalSql = $table . $sql . ' where id in ( ' . implode(',', $ids) . ' )';
            $result = $this->query($finalSql);
            return $result->executeResult;
        } else {
            return false;
        }
    }