LAST_INSERT_ID() returning 0 when ON DUPLICATE KEY UPDATE but only in Retool

馃挰 Queries and Resources
2

For anyone reading, I was able to solve this issue.

I have learned about LAST_INSERT_ID(); from here MySQL LAST_INSERT_ID() Function where it says

The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.

However it seems like the bold part is wrong, the function returns 0 for updated rows.

Here is the solution Wrong value returned in insertId on INSERT INTO...ON DUPLICATE KEY UPDATE 路 Issue #647 路 mysqljs/mysql 路 GitHub

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID()
function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not
meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the
AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
1 Like