使用PHP在一个SQL查询中执行多个UPDATE

问题描述：

So I have had a look at HERE, but it seems a little convoluted for the simplicity of what I am doing.. At maximum I would be dealing with a hundred items to update (and most of the time its going to be more like 40)

Currently I have something like this

$sql_update = '';
for($x = 0; $x < count($nodes); $x++){
  if($nodes[$x]['loaded'] == 'true'){
    if($nodes[$x]['changed'] == 'true'){
        $sql_update .= 'UPDATE `genetic_decomp`.`tbl_node2view` SET `x` = "'.$nodes[$x]['location']['x'].'", `y` = "'.$nodes[$x]['location']['y'].'" WHERE `tbl_node2view`.`id` = "'.$nodes[$x]['id'].'";'; 
        $sql_update .= 'UPDATE `genetic_decomp`.`tbl_nodes` SET `name` = "'.$nodes[$x]['name'].'", `type` = "'.$nodes[$x]['type'].'" WHERE `tbl_nodes`.`node_id` = "'.$nodes[$x]['id'].'";';
    }
  }
}
if($sql_update != ''){
    $sql_result=mysql_query($sql_update,$connection) or exit("Sql Error".mysql_error());
}

Now when i get it to print out the output in just an echo $sql_update and then paste the output into the SQL box in MAMP it works fine.. goes through and updates the lines in the two tables i want

however when i run the above code it spits back:

Sql Error
You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `genetic_decomp`.`tbl_nodes` SET `name` = "lala", `type` = "p" WHERE `tbl' at line 1

what am i doing wrong? is there a better way of doing this?

所以我看了这里，但对于我正在做的事情的简单性似乎有点令人费解。最多我会处理一百个要更新的项目（大部分时间它会更像40） p>

目前我有类似的东西 p>

  $ sql_update =''; 
for（$ x = 0; $ x＆lt; count（$ nodes）; $ x ++）{
 if（$ nodes [$ x] ['loaded'] =  ='true'）{
 if（$ nodes [$ x] ['changed'] =='true'）{
 $ sql_update。='UPDATE`genetic_decomp``tbl_node2view` SET`x` =“'  。$ nodes [$ x] ['location'] ['x']。'“，`y` =”'。$ nodes [$ x] ['location'] ['y']。'“WHERE`tbl_node2view  `.``` =“'。$ nodes [$ x] ['id']。'';';;  
 $ sql_update。='UPDATE`anetic_decomp``tbl_nodes` SET`name` =“'。$ nodes [$ x] ['name']。'”，`type` =“'。$ nodes [$ x  ] ['type']。'“'WHERE`tbl_nodes` .node_id` =”'。$ nodes [$ x] ['id']。'“;'; 
} 
} 
} 
} 
if（  $ sql_update！=''）{
 $ sql_result = mysql_query（$ sql_update，$ connection）或exit（“Sql Error”.mysql_error（））; 
} 
  code>  pre> 
 \  n 现在，我只需要在 echo $ sql_update  code>中打印出输出，然后将输出粘贴到MAMP中的SQL框中就可以了。它可以正常运行并更新 我想要的两个表 p> 
 
 
然而，当我运行上面的代码时它会吐出来： p> 
 
 

  Sql Error 
你的错误在你的 SQL语法;  
 
检查与您的MySQL服务器版本对应的手册，以便在'UPDATE`genetic_decomp``tbl_nodes` SET`name` =“lala”，`type` =“p”WHERE`tbl'附近使用正确的语法 
  code>  pre> 
 
 我做错了什么？  
有更好的方法吗？ p> 
  div>

答

Your SQL looks syntactically correct (unless I've missed something simple). The actual problem is because you're using mysql_query() - which does not support multiple statements; therefore, you can't run two UPDATE queries in one with this method.

From the manual:

mysql_query() sends a unique query (multiple queries are not supported)

On the same note, the mysql_ methods are being deprecated so I (and the community) would suggest you update your code to use mysqli_ or PDO methods - both of which support multiple queries in a single statement.

If you need to stick with mysql_query() (instead of restructuring your entire application), just split the queries and run them back-to-back.

答

You need to update them separately if you're using mysql_query.

答

As an alternative to mysqli I have found this: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

The idea

UPDATE categories
    SET display_order = 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'
    END
WHERE id IN (1,2,3)

Real life example

// An array containing the category ids as keys and the new positions as   values
$display_order = array(
    1 => 4,
    2 => 1,
    3 => 2,
    4 => 3,
    5 => 9,
    6 => 5,
    7 => 8,
    8 => 9
);

$ids = implode(',', array_keys($display_order));
$sql = "UPDATE categories SET display_order = CASE id ";
foreach ($display_order as $id => $ordinal) {
    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;

My case

What I received as array

Array
(
    [0] => stdClass Object
        (
            [movement_id] => 278
            [leg_miles] => 3.5
            [leg_km] => 5.63
        )

    [1] => stdClass Object
        (
            [movement_id] => 279
            [leg_miles] => 
            [leg_km] => 
        )

    [2] => stdClass Object
        (
            [movement_id] => 280
            [leg_miles] => 
            [leg_km] => 
        )

)

My function (have to work with floats on sprintf)

    //get ids string
    $ids = "";
    foreach ($movementsData as $movement) {
        $ids .= $movement->movement_id.",";
    }
    $ids = rtrim($ids, ",");

    //MySQL query construct
    $sql = "UPDATE movements SET movement_miles = CASE movement_id ";

    foreach ($movementsData as $movement) {
        $sql .= sprintf("WHEN %d THEN %.2f ", $movement->movement_id, $movement->leg_miles);
    }

    $sql .= "END, ";

    $sql .= "movement_km = CASE movement_id ";

    foreach ($movementsData as $movement) {
        $sql .= sprintf("WHEN %d THEN %.2f ", $movement->movement_id, $movement->leg_km);
    }

    $sql .= "END ";

    $sql .= "WHERE movement_id IN (".$ids.")";

Final query result

UPDATE movements SET 
    movement_miles = CASE movement_id 
        WHEN 278 THEN 3.50 
        WHEN 279 THEN 0.00 
        WHEN 280 THEN 0.00 
    END, 
    movement_km = CASE movement_id 
        WHEN 278 THEN 5.63 
        WHEN 279 THEN 0.00 
        WHEN 280 THEN 0.00 
    END 
    WHERE movement_id IN (278,279,280)

答

In php, you use multi_query method of mysqli instance.

From the php manual

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

使用PHP在一个SQL查询中执行多个UPDATE

相关推荐