使用单个查询更改多个MySQL字段 - 产品代码销售

Question

I have a site with products I sell and each product has its own product code. The problem is that recently we changed all product codes to all products.

Because all sales inserted in MySQL before today used the old product code, when I try to get a report to see how many of one product has been sold system find 0 because it looks for the new product code while older sales was inserted with the old one.

Solution:

Even if it is a pain there is no other way than updating all products sold and inserted in MySQL updating the old product code with the new one this way it will work fine.

I need to update like this:

    $update = mysqli_query($database, "
update `sales` SET code = 0001 WHERE `code` = '4574645448458'
");

The only problem is that it updates only the first product with this product code but I have houndreds of products sold with the same product code...

How to solve this in some bulk way?

examples of what I will change:

code 4574645448458 for 0001 code 4574645448459 for 0002

and so on

Answer 1

Here is an example of the suggestion from Marc B.
Use a prepared statement and execute it with each pair of "old/new" values.

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again.

For example:

// define an array with all of the code changes as "key/value" pairs
$changes=array(
    '4574645448458' => '0001',
    '4574645448459' => '0002',
    ....
);

// define the query string with placeholders
$sql="UPDATE `sales` SET `code`=:new_code WHERE `code`=:old_code;";

// prepare the statement and set up variables to be bound upon execution
$q=$database->prepare($sql);
$q->bind_param(':new_code',$new_code);
$q->bind_param(':old_code',$old_code);

// iterate through "changes", defining the "old" and "new" values for each code change
// execute the prepared statement with each pair of values
foreach ($changes as $old_code => $new_code) {
    $q->execute();
}