Search code examples
mysqlsqlcase-statementlast-insert-id

MySQL INSERT and SELECT LAST_INSERT_ID() in one line

I was wondering if there was a way to change this statement

INSERT INTO [table] VALUES ([values]);
SET @last_id = LAST_INSERT_ID();

Into a single line. Something such as

SELECT 
CASE WHEN ( SELECT COUNT(id) FROM [table] WHERE id=1 ) > 0 
THEN 'false' 
WHEN ( INSERT INTO [table] VALUES ([values]) )
THEN LAST_INSERT_ID()
END INTO @myid;

(which could effectively be written in a single line)

Would the WHEN ( INSERT INTO ...) statement is attempted, will it return a value that we can check?

Will it also execute the INSERT even if it is within a CASE statement?

Solution

  • In the end, I had no choice but to split the code to two lines using on of the suggestions in the comments.

    INSERT INTO table (id,....) VALUES (1234,........) ON DUPLICATE KEY id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID into @myid