meh meh - 8 months ago 59
SQL Question

Get both insert_id and updated row id in insert_update query

I have a query like this:

SET @uids = '';

INSERT INTO tbl1 (name,used,is_active)
VALUES (1,0,0),(2,0,0),(24,0,0)
, used = (SELECT @uids := concat_ws(',', LAST_INSERT_ID(), @uids))
, used = used+1
, is_active = CASE WHEN used > 3 THEN 1 ELSE 0 END;

SELECT @uids;

See here to figure out the way of getting updated row id.

I get updated row ids' in
if it updates any rows but if a row is inserted, I can't get the id of that. So how to get both inserted row id and updated row id?

Or how to execute
(SELECT @uids := concat_ws(',', LAST_INSERT_ID(), @uids))
in insert before


Time's short and we are long

You can't do it, because there is no way to fill @uids while inserting which needs a select clause and you are not allowed to use a select clause within an insert statement unless your query can be transformed into an INSERT ... SELECT.

Long answer

As long as you don't try to insert mixed values that may result in both updating and inserting (which probably you do) there is a nasty but safe way you can go with:

SET @uids := '';
INSERT INTO `tbl1` (name, used, is_active)
    VALUES (1,0,0),(2,0,0),(24,0,0)
        is_active = CASE WHEN used > 3 THEN 1 ELSE 0 END,
        id = LAST_INSERT_ID(id),
        used = used + 1,
        id = (SELECT @uids := concat_ws(',', LAST_INSERT_ID(), @uids));
SELECT @uids, LAST_INSERT_ID() as f, MAX(id) as l from `tbl1`;

Being not so tricky, you have two values at the end:

  1. LAST_INSERT_ID() as f is the first inserted row ID
  2. MAX(id) as l which is last inserted row ID

So with that two boundaries you surly have all inserted rows IDs. Saying that it has drawbacks and that is you always have a LAST_INSERT_ID() value even if rows only were affected by update statement. However as you tagged your question with there was a chance to get benefit from mysqli_affected_rows while doing a multi_query but I couldn't produce expected return values from mysqli_affected_rows as is documented by MySQL:

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

You can try it yourself and see if it works. If you get an expected return value then you can understand if your query has done some updates or inserts and read results based on that

As my short answer, there is no correct way to do it within the same query context but may be doing it programatically is neater? (though I don't bet on its performance)

$values = [[1, 0, 0], [2, 0, 0], [24, 0, 0]];
$insertIDs = [];
$updateIDs = [];

foreach ($values as $v) {
    $insert = $mysqli->prepare("INSERT INTO `tbl1` (name, used, is_active) VALUES (?, ?, ?)");
    $insert->bind_param('ddd', $v[0], $v[1], $v[2]);
    if ($insert->affected_rows == -1) {
        $update = $mysqli->prepare("UPDATE `tbl1` SET id = LAST_INSERT_ID(id), used = used + 1, is_active = CASE WHEN used > 3 THEN 1 ELSE 0 END WHERE name = ?"); // considering `name` as a unique column
        $update->bind_param('d', $v[0]);
        if ($update->affected_rows == 1)  {
            $updateIDs[] = $update->insert_id;
    } else {
        $insertIDs[] = $insert->insert_id;


Example output:

array(1) {
array(1) {

One another workaround could be using MySQL triggers. By creating an AFTER INSERT trigger on table tbl1, you are able to store IDs for later use:

CREATE TRIGGER trigger_tbl1
    ON `tbl1` FOR EACH ROW
    UPDATE `some_table` SET last_insert_ids = concat_ws(',', LAST_INSERT_ID(), last_insert_ids) WHERE id = 1;