Update multiple rows/columns with a single query

[tsangaris]

Hi,

 

I have a table with the following data:

 

id   name   hours  annual_leave

================================= 

1   Chris      10          0

2   Tony      15          0

3   Mark      9            0

4   John      23          0

5   Lee        8            0

 

What i want to achieve is:

 

If an employee worked 10 hours or more then reward him/her with 1 day annual leave. By default the value of annual_leave is "0".

 

So i find all eligible employees and store their IDs in a table:

 

$eligible = array ( 0 => 1, 1=>2 , 2=>4);

 

 

id   name   hours  annual_leave

================================= 

1   Chris      10        1

2   Tony      15        1

3   Mark      9          0

4   John      23        1

5   Lee        8          0

 

What i want to do is to create a single UPDATE query that will find all 3 employees and change the annual_leave value from 0 to 1.

 

I can do this easily within the loop i use to create the $eligible table.. But i have the following questions:

 

a) can i use a single UPDATE query to update multiple rows/columns in a table?

b) what is better? a single query to update multiple rows/columns or multiple queries that update a single row/column per time

c) can i combine PHP and MYSQL to syntax a query? because in case the $eligible array carries 1000 entries i will need to iterate through it using a FOR or WHILE loop..

 

Any other comments will be appreciated..

 

 

 

 

[kicken]

If you have the ID's of the rows you want to update to 1, then you'd just specify them in the WHERE clause.

UPDATE table
SET
  annual_leave=1
WHERE
  id IN (1,2,4)

You can do a single update query that includes the calculation also.

UPDATE table
SET
   annual_leave=CASE WHEN hours >= 10 THEN 1 ELSE 0 END

Edited February 21, 2015 by kicken

[tsangaris]

So lets say that i want mix the MYSQL query with some PHP how do i do it?

 

As you suggested before:

 

UPDATE table
SET
annual_leave=1
WHERE
id IN (1,2,4)

 

 

I need it to be like:

 

UPDATE table
SET
annual_leave=1
WHERE
id IN [some PHP code giving the same result as (1,2,4)]

 

the ids are listed in a PHP array..

 

Thanks!

[Ch0cu3r]

Use implode to convert the array of ids into a comma delimited list

$ids = array(1, 2 , 4);

$sql = 'UPDATE table
SET
annual_leave=1
WHERE
id IN ('.implode(',', $ids).')';