Home / Questions / 233871
Accepted
Antonio Ruiz
Asked: 2020-01-31 07:25:24 +0800 CST 2020-01-31 07:25:24 +0800 CST

How to do a massive Update in MySql with Where?

  • 772

I am reading an excel file from PHP, I am collecting the fields that interest me and saving the values ​​in a variable to later build the update.

foreach ($rowData as $key => $value) {
  blah blah...
  $sql2 .="(pcompra='".$pcompra."', margen='".$margen."')  WHERE iduser=45 AND 
  referencia='".$referencia."', ";
}

So far so good, the problem is building the query, because I know that the above is wrong and I don't know how to build it so that the bulk update works correctly.

This is how I have inserted the 6000 records in a single query, but I don't know how to do the UPDATE.

My table has a series of fields, but among them I have:

  • ID (unique) <- but I don't know it in the update.
  • iduser
  • reference <- is unique depending on the iduser
  • pbuy2 <- I want to update it
  • margin <- I want to update it

Now I have 6000 products to update. How could I update the 'pcompra2' and 'margin' fields in the 6000 products according to iduser and reference in a single sentence ?

I try something like this:

UPDATE stocks 
SET 
      (pcompra='5', margen='0.2')  WHERE iduser=45 AND referencia='00257', 
      (pcompra='6', margen='0.1')  WHERE iduser=45 AND referencia='00258', 
      (pcompra='2', margen='0.2')  WHERE iduser=45 AND referencia='00259', 
      .... hasta 6000.
php

1 Answers

  1. Best Answer

    Well aside from the solutions of the companions I have invented a "solution" for those who have access to mysql.

    Notice that it is very inelegant but quite fast.

    In PHP I have created the following loop:

    if($cont>=100 AND $cont<=1000){
  echo $sql2 ="<br/>UPDATE stocks SET pcompra2='".$pcompra2."', margen='".$margen."' WHERE iduser='".$iduser."' AND referencia='".$codigo."';";
}

    This creates 900 (or as many as you like) UPDATE queries and displays them in the browser window.

    Well, I copy and paste in mysql and that's it, in 5 seconds those 900 of the 6000 updated.

    Then it's time to change the numbers in the loop.

    • 0