1

I am trying to get insertedid after I insert multiple row in mysql table by using this query

insert into sometable (id , candidateid , createdby)
    values ('61','3175','1425'),('60','3175','1425'),('42','3175','1425'),('61','3176','1425'),('60','3176','1425'),('42','3176','1425') OUTPUT INSERTED.id

but I am getting sql syntax error

code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTPUT INSERTED.id' at line 2",
  sqlState: '42000',

how can I get insertion id

Saurabh
  • 1,592
  • 2
  • 14
  • 30
  • Does this answer your question? [How can I fix MySQL error #1064?](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) – Shmiel Jun 26 '22 at 15:17
  • 1
    Mysql doesn't have this functionality. In your example you are inserting specific id's, so you have them already – HoneyBadger Jun 26 '22 at 15:22
  • you have an ` OUTPUT INSERTED.id` at the end remove it – nbk Jun 26 '22 at 15:24

1 Answers1

3

The OUTPUT INSERTED syntax is a Microsoft SQL Server thing. That syntax is not supported by MySQL.

In your example, you have set the id values explicitly, so there's no need for them to be returned. Just get them from your own values which you apparently already knew before you did the INSERT.

You might be relying on auto-incremented id's, which are generated during the INSERT.

To get the auto-increment id of the most recently inserted row, use the LAST_INSERT_ID() function in a separate query after your INSERT.

LAST_INSERT_ID() returns only the first id generated during your insert. If you do a multi-row insert, then it's up to you to extrapolate the subsequent id's. They are usually consecutive values. For example, the MySQL JDBC driver relies on them being consecutive values so it can return the set of id's after a batch insert.

But they are not guaranteed to be consecutive values if you have changed innodb_autoinc_lock_mode=2 which allocates the auto-inc values in an "interleaved" manner, so concurrent inserts may grab a value from the sequence in between the set your multi-row insert is generating. This is not the default mode, so you would have set it deliberately.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828