0

I have a table tbl

|id|points|
 1   15
 2   35
 3   445
 4   42

Now if i have an array like

array (2=>10,3=>825,4=>48)

And i want to change the points so that the tbl looks like this.

|id|points|
 1   15
 2   10
 3   825
 4   48

I can change the values using multiple queries but can anyone show how to do this with one single query ?

Slim Shady
  • 1,045
  • 2
  • 12
  • 38
  • What is your rule for changing the values? – frlan Mar 12 '15 at 20:14
  • 2
    possible duplicate of http://stackoverflow.com/questions/3432/multiple-updates-in-mysql – Paul Griffin Mar 12 '15 at 20:14
  • @frlan asks the right question, but I might also ask, why do you feel the need to use a single statement? You can have multiple statements in the same transaction if that is what worries you. – Chris Hep Mar 12 '15 at 20:15
  • "how to do this with one single query ?" .. using a case statement. – xQbert Mar 12 '15 at 20:16
  • Rule is i have an `array (1=>05,2=>10,3=>825,4=>48)` where the `key` is `id` and value is `points` @frlan – Slim Shady Mar 12 '15 at 20:17
  • @HepC anything which is more optimised will do the work....may it be `multiple statements with same transaction` or `single query` – Slim Shady Mar 12 '15 at 20:19
  • Really, if you put all your updates into one transaction, most likely you will have the best way of doing it plus the benefit of easy understanding code. If you don't want, you can put your array into a subselect in combination with a case... but well... no. – frlan Mar 12 '15 at 20:21

2 Answers2

3

Use a case statement...

Update tbl
set points = CASE 
  WHEN ID = 1 then 05 
  when ID = 2 then 10 
  when ID = 3 then 825 
  when ID = 4 then 48 END

Working fiddle: http://sqlfiddle.com/#!9/6cb0d/1/0

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • But if i have thousands of `ID` in my array then it wont be helpful! – Slim Shady Mar 12 '15 at 20:23
  • you may run into a buffer overrun; but it wouldn't be a problem with the SQL execution. Alternative approach would be to write the array to a temp table and update based on it. Additionally if any of the points are the same you could use when `ID IN (1,2) then point`... – xQbert Mar 12 '15 at 20:25
-1

First you have to upload your array to a temp. table. Let's call it new_list, than you can do something like 

UPDATE table as t
SET points = (SELECT points FROM new_list WHERE t.id = new_list.id)

To be honest, I have no clue whether this is running on MySQL so you might need to put some own thoughts into.

As it appears the data is inside an PHP array, I think maybe rendering something like 

UPDATE table SET points = $points WHERE id = $id1
UPDATE table SET points = $points WHERE id = $id2
UPDATE table SET points = $points WHERE id = $id3
frlan
  • 6,950
  • 3
  • 31
  • 72