Search code examples
sqldbeavermariadb-10.2

SQL INSERT is not working with OUTPUT INSERTED statement

My goal is to insert a new row in a database table and immediately get back the ID of the inserted row. I saw some other posts on stackoverflow that suggested to use this command:

INSERT INTO selecttable (name) OUTPUT INSERTED.ID VALUES ('aName')

Unfortunately, the execution of this command fails with the following error message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUTPUT INSERTED.ID VALUES ('aName')'

The execution of the command without the output statement worked well:

INSERT INTO selecttable (name) VALUES ('aName')

Do you have an idea, why the command above is not working?

Solution

  • In general, I think you use LAST_INSERT_ID():

    INSERT INTO selecttable (name)
    VALUES ('aName');

SELECT LAST_INSERT_ID();

    This is an imperfect equivalent construct. For instance, it doesn't work so well with multiple rows. But it is the MariaDB/MySQL solution to this problem.