Updating several records in a database with increasing value

I need to update several records in a table, and am hoping to accomplish it in one query.

I have a php array of id's for a few of the records in my table. The array is in a specific order, and I need to update a value for each id with the position/key that the id is in the array. I could easily loop through each value in the array and run a query for each record that needs to be updated, but I am looking for a way to possibly update each record in one query.

The following will give me the desired result, but I am looking for a one query solution.

$ID = array('3','2','6','5','9');
for($i = 0; $i <= 4; $i++){
    $q = "UPDATE table SET blah = ".$i." WHERE id = ".$ID[$开发者_StackOverflow社区i];
    mysql_query($q) or die();   
}

Thanks.

---

2 queries:

First only needed in case you have used @var already.

SET @var := -1;

And then (note FIND_IN_SET wants a comma-separated string):

UPDATE tablename 
SET blah = @var := @var +1
WHERE id IN (3,2,6,5,9)
ORDER BY FIND_IN_SET(id,'3,2,6,5,9');

If you have an array with key=>postition pairs, this could be used:

$array = array(2 => 40,3 => 12,5 => 8,6 => 9,9 =>13129);
mysql_query("
   UPDATE bla
   SET blah = ELT(
     FIELD(id,".implode(',',array_keys($array))."),
      ".implode(',',$array).")
   WHERE id IN (".implode(',',array_keys($array)).")");

---

A big long ugly if or case statement would do the trick:

UPDATE ...
SET blah = IF(id=3,1,IF(id=2, 2, IF(id=6,3, IF....))))
WHERE id IN (3,2,6,....)

it'd quickly become unmanageable, but it would accomplish things in a single query. A slightly cleaner, more portable alternative:

UPDATE
SET blah = CASE id WHEN 3 THEN 1 WHEN 2 THEN 2 WHEN 6 THEN 3 .... END CASE
WHERE id in (3,2,6,...)

---

Try this:

INSERT INTO tbl_name (idCol, valCol) VALUES (id1, value1),(id2, value2)
ON DUPLICATE KEY UPDATE
  idCol = VALUE(idCol),
  valCol = VALUE(valCol)

You could also use a stored procedure, passing it a string of the ID's:

CREATE PROCEDURE updateItems( firstNum INT NOT NULL, ids TEXT NOT NULL) BEGIN
   @id := ... #  Some expression to get the first ID.
   WHILE id != "" DO
      SET ids := ... #  Some expression to get the rest of the string.
      UPDATE table SET blah = firstNum WHERE id = @id;
      SET firstNum = firstNum +1;
      @id := ... ;
   END;
END $$

Also, there was one trick with LAST_INSERT_ID() - if you call it with a param, like LAST_INSERT_ID( 123 ), successive call to LAST_INSERT_ID() will give you 123. Could be used together with @variables to achieve that goal.

You might get some inspiration here: http://ondra.zizka.cz/stranky/programovani/sql/mysql_stored_procedures.texy

---

It's impossible in this way ...Well, it's possible but I'd suggest you not to do it, 5 queries aren't horrors

---

$q = "UPDATE table SET blah = ".$i." WHERE id in ('3','2','6','5','9')";

---

If you are concerned about performance, you should take a look at using prepared statements and transactions.

There is most likely a solution to doing this in one single query, but it would most likely get really messy.

---

your better of using foreach then you don't have to set a $i limit. i think its quick to do it this way

$id = array('3','2','6','5','9');
$i=0;

 foreach ($id as $v){

    $q = "UPDATE table SET blah = ".$i++." WHERE id = '$v'";
    mysql_query($q) or die();   

 }