有没有办法更新行以添加批次ID并返回该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)?

Answer 1

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).