Is there a way to update rows to add a batch ID and return that ID?

Question

I'd like to retrieve a number of rows from a MySQL table, and remember which ones I've retrieved later. (I'm sending these to an API and I need to track whether they were accepted or not).

The way I'd like to achieve this is to use an UPDATE query in order to create a batch_id for 100 records or so. I'd also like to get the batch_id returned.

Here's what I've come up with so far (complete PHP function):

function get_batch() {
    global $DB;
    $q = $DB->prepare("UPDATE my_table 
                       SET batch_id = LAST_INSERT_ID(MIN(id)) 
                       WHERE batch_id = ''
                       LIMIT 100");
    $q->execute();
    return $DB->lastInsertId();
}

This is an invalid use of a group function (because I can't use MIN on all rows at the same time as updating individual rows, presumably).

I could use a subquery to find the lowest ID, but I wondered if there was a better way of doing this?

I'm also not sure if my use of LAST_INSERT_ID is correct in this context (setting it to retrieve it later)?

As an aside, note that LIMIT without ORDER BY is fairly meaningless. — Strawberry, Jul 13 '18 at 11:17

sdexp · Answer 1 · 2018-07-13T11:07:05.577

0

You could do two queries, one to get the rows you're going to update, and the other to update them.

SELECT * FROM my_table WHERE batch_id='' ORDER BY id ASC LIMIT 100

Then you can do something like a for loop where you do each one individually with...

UPDATE my_table SET batch_id='value' WHERE id = $i

Or, if the ids of the rows are going to be in a block with no gaps you could do something like...

UPDATE my_table SET batch_id='value' WHERE id >= $min_value_id AND id <= $max_value_id

See, this question.

edited Jul 13 '18 at 11:07

answered Jul 13 '18 at 11:02

sdexp

756
4
18
36

Why would 2 queries and individual updates be better? – user3783243 Jul 13 '18 at 11:05
I'd rather a `SELECT` and `UPDATE` within the same query to avoid a race condition. As stated above though, I can use a sub-query to get around this. – Ben Jul 13 '18 at 11:30

score 0 · Accepted Answer · answered Jul 13 '18 at 12:42

In the end, the best solution I could come up with, was the following:

function get_batch() {
    global $DB;
    $q = $DB->prepare("UPDATE my_table
                       SET batch_id = (
                         SELECT batch_id FROM (
                           SELECT LAST_INSERT_ID(MIN(id)) AS batch_id
                           FROM my_table WHERE (batch_id = 0)
                         ) b
                       )
                       WHERE batch_id = 0 ORDER BY id LIMIT 100");
    $q->execute();
    return $DB->lastInsertId();
}

Note that the sub query required another sub query within (b), which is something to do with getting MySQL to copy the sub query result to a separate table space (related question).

Is there a way to update rows to add a batch ID and return that ID?

2 Answers2