Updating multiple rows in MySQL without a loop

Question

I'm trying to write up an email notification system for a job recruitment site I made and am currently looking at grouping a certain amount of jobs together before sending an email to the candidate

I have a table which I've called candidate_to_job which contains the candidate_id, job_id and an "emailed" boolean

What I'm struggling with is updating that table when a new job is posted and emails are sent out. So far when a new job is posted I run the following:

SELECT     c2j.candidate_id, c2j.job_id, j.title 
FROM       " . DB_PREFIX . "candidate_to_job c2j 
LEFT JOIN  " . DB_PREFIX . "job j 
       ON  (j.job_id = c2j.job_id) 
WHERE      c2j.emailed = 0

Then through PHP I group them all together so I have an array looking something like this:

$candidates = array(
    1 => array(1,2,3),
    2 => array(1,3),
    3 => array(4,5,6)
);

With the array key being the candidate ID and the value an array of job IDs

What I want to do using that array - after the emails have been sent - is update the candidate_to_job table setting emailed to true, e.g candidate_id 1 would have emailed set to true for job_ids 1, 2 and 3

Is there a way I can do this in one query? I've looked at WHEN CASE statements but I don't think that applies in this case? I really don't want to run multiple queries per candidate because there could potentially be thousands!

ypercubeᵀᴹ · Accepted Answer · 2012-08-02 12:30:19Z

3

You can run one UPDATE query per group provided that the group can share the same WHERE criteria and the same update values.

UPDATE tbl SET value = TRUE WHERE id IN(1,2,3,4,5,6);
UPDATE tbl SET value = FALSE WHERE id IN(7,8,9,10,11);

Or you can use the WHEN clause or even some IF clauses provided that the criteria are simple enough.

UPDATE tbl SET value = IF(id = 1) WHERE id IN(1,2);
UPDATE tbl
    SET value = CASE
                    WHEN id IN (1,2,3,4,5,6) THEN TRUE
                    WHEN id IN (7,8,9,10,11) THEN FALSE
                    ELSE value
                END
    WHERE id IN (1,2,3,4,5,6,7,8,9,10,11);

Having possibly thousands of WHEN cases, may be a hassle to build/change, I'd go with the first option:

Flip the old key=>value array and keep all ids connected to a value:

foreach($list AS $id => $value) {
    $list2[$value][] = $id;
}

Iterate through the value=>keys array and build UPDATE queries that can bulk update the value for all keys at once.

edited Aug 2, 2012 at 12:30

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

answered Aug 2, 2012 at 12:09

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

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

2 Comments

Andy Over a year ago

I never even thought of flipping the array around. That's a good idea, I can make another array with the key as the job id then the value an array of candidate_ids - thanks!

Mihai Stancu Over a year ago

@ypercube Thank you, i never thought of using IN with a CASE, SQL syntax is less intuitive as to what exactly is a fixed flow-control-construct vs. predefined functions vs. statement-syntax, etc..

ToBe · Accepted Answer · 2012-08-02 12:28:02Z

0

If you are using mysqli extension in PHP instead of mysql, you can also write mutliple statements seperated by ';' and send all of them in one query. This might result in slightly simpler code than a complex UPDATE with cases.

I dont think this costs much more performance than one single complex UPDATE statement.

answered Aug 2, 2012 at 12:28

ToBe

2,6791 gold badge20 silver badges31 bronze badges

1 Comment

Mihai Stancu Over a year ago

As a rule of thumb i always "compact" multiple statements that I can run at once because the communication protocol between PHP and MySQL works over TCP (it is usually a local DB but it's still TCP) and limiting those TCP communications is just as good an idea as limiting disk IO or any other expensive resource.

pyrospade · Accepted Answer · 2012-08-02 12:53:36Z

0

Is this what you're after? You can do a join using from and where without having to dynamically generate SQL in PHP.

UPDATE " . DB_PREFIX . "candidate_to_job c2j 
SET emailed = 1
FROM  " . DB_PREFIX . "job j 
WHERE j.job_id = c2j.job_id and
      c2j.emailed = 0

answered Aug 2, 2012 at 12:53

pyrospade

8,1004 gold badges38 silver badges52 bronze badges

4 Comments

Andy Over a year ago

Isn't that just the same as UPDATE " . DB_PREFIX . "candidate_to_job SET emailed = 1 WHERE emailed = 0? There doesn't seem to be any criteria on which rows to update

pyrospade Over a year ago

And implicit filter is applied to any matches on the table job. If you created your PHP array from the result of your first query then this should work.

Andy Over a year ago

I think you may have misunderstood. Every job is in the job table, so the WHERE j.job_id = c2j.job_id is always going to be true isn't it? The entries are added to the candidate_to_job table when a job is posted and it matches the candidates job criteria

pyrospade Over a year ago

Could you provide some more context as to how this is implemented? In your question you state that you run the SQL then use PHP to stick the results into an array of arrays. How are you determining what goes in the array?

Collectives™ on Stack Overflow

Updating multiple rows in MySQL without a loop

3 Answers 3

2 Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related