PHP/MYSQL: batch update multiple values

Question

I have figured out how to do a batch update when the value is the same for each record using:

UPDATE tbl SET col1='foo1' WHERE id IN (1,2,3)

If I have a comma delimited string of values, that match up to the ids, can I do a batch update that updates the values differently as in

UPDATE tbl SET col1='1,0,1' WHERE id IN (1,2,3)

Thanks for suggestions:

Edit:

The html page that sends the data to this query consists of checkboxes as follows:

<input type="checkbox" name="id[]" value="1"><input type="hidden" name="col1[]" value=0> 
<input type="checkbox" name="id[]" value="2"><input type="hidden" name="col1[]" value=1>
<input type="checkbox" name="id[]" value="3"><input type="hidden" name="col1[]" value=0>

etc. up to 20 boxes.

On the server side, the posted arrays are converted to comma delimited strings using implode so I end up with the two strings, 1,0,1 for the values and 1,2,3 for the ids. But the user could check up to 20 boxes from this page. Maybe I have to manipulate the arrays somehow. Note in the real example, the ids are not 1,2,3, but could be something like 221, 433, 512, 600 etc., depending on what user checks

Bakly · Accepted Answer · 2013-10-29 09:33:39Z

8

the second query will update each row 1, 2,3 to the same value '1,0,1'

i think what you need is

  UPDATE mytable
    SET myfield = CASE other_field
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END 
WHERE id IN (1,2,3)

reference http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

edited Oct 29, 2013 at 9:33

answered Nov 5, 2012 at 13:03

Bakly

6506 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1260310 Over a year ago

do you need to iterate a new When case for each element you get. Number of elements/ids is unknown as it is coming from a page where user checks boxes and can check from 0 to 20. See edit to question above for how the data is coming

AnandPhadke · Accepted Answer · 2012-11-05 13:13:12Z

0

This is perfect when col1 is of varchar datatype.

UPDATE tbl SET col1=CASE WHEN id=1 then 1,
                         WHEN id=2 then 0,
                         when id=3 then 1 END  WHERE id IN (1,2,3)

edited Nov 5, 2012 at 13:13

answered Nov 5, 2012 at 12:59

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

4 Comments

Muthu Kumaran Over a year ago

I think all 3 id's (1,2,3) will have same values "1,0,1". It won't automatically match up with each id's.

Muthu Kumaran Over a year ago

Output will be "1,0,1" for all updating records

user1260310 Over a year ago

I want it to set different boolean values depending on user input of 0 or 1 etc. Data type of col1 is tinyint(1)

firestoke Over a year ago

the query string is wrong! you can't add comma after the "WHEN" statement, or it will show up mysql error! modify it to this: UPDATE tbl SET col1=CASE WHEN id=1 then 1 WHEN id=2 then 0 when id=3 then 1 END WHERE id IN (1,2,3)

Collectives™ on Stack Overflow

PHP/MYSQL: batch update multiple values

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related