Update several records of a column in a single SQL query


I want to reduce the number of UPDATEs that I make from my application to the table ventas of my database. This UPDATE sends a value to the record in the ordenadores column, as long as it matches the values passed to id and procesador . I have thousands of records with different values, so I end up doing thousands of UPDATEs was to finish the task.


$valor_id = $_POST['id'];
$procesador = $_POST['procesador'];
$valor_n = $_POST['valor'];

$sql = "UPDATE ventas SET ordenadores = ".$valor_n." WHERE valor_id = ".$valor_id." AND procesador = ".$procesador."";

$result = $db_connection -> query($sql);

Is it possible to update, in a single UPDATE, different rows with different values in the ordenadores column?

Thank you very much in advance.

asked by Pablo García 16.05.2016 в 21:13

2 answers


First of all and VERY important: never build an SQL statement based on parameters that are sent in the POST. This results in problems of sql injection . Therefore, whenever parameters are passed to an SQL it should be as follows (using PDO ) :

$stmt = $pdo->prepare('UPDATE ventas SET ... WHERE id = :id');
$stmt->execute(array('id' => $valor_id));
$result = $stmt->get_result();

In the example, the parameter is in the variable $valor_id and is represented by the text :id in the SQL statement.

Now, going to the problem and given the conditions you would have to answer that you can NOT perform the operation you want.

As mentioned in the comments of the question, there are several options to implement, and they depend a lot on some more data of the problem that they do not have in order to answer correctly.

Option 1

If for example you had a table similar to the following one (let's call it TablaX):

valor_id | procesador | valor_n
xxx      | 'intel'    | yyy
aaa      | 'amd'      | bbb

you could do something like the following:

    ON ventas.valor_id = TablaX.valor_id AND ventas.procesador = TablaX.procesador
SET ventas.ordenadores = TablaX.valor_n

Option 2

Another option would be to group values. This way you would not delete all the UPDATE but you would have less. For example, group all the updates of 'intel', then all of 'amd', etc.

I can think of something like the following:

UPDATE  ventas
SET     ordenadores = IF(valor_id = :valor_id1, :valor_n1, IF(valor_id = :valor_id2, :valor_n2, ..), ..),
WHERE   procesador = 'intel'

Using something of the style, you could pass execute a array with all the possible values: id_value,: value_ni (with i from 1 to N the number of different values)

Surely there are more options but as already mentioned, it depends a lot on the context of the problem.

I hope it has been useful.


answered by 17.05.2016 / 02:14

Of course, in a single query you can update all the fields you want from a table, for example:

 columna_1 = $value, columna_2 = $value, ... ,columna_n = $value
 registro_en_comun_entre_filas = $valor_comun;

I hope you solve your question, anyway it seems to me that your question is incomplete and it would be better if you provide more information, to give an even more precise answer.

answered by 16.05.2016 в 22:04