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

Queries and Resources
1

Hi guys,

I have this simple query:

INSERT INTO kunden (kundennr, art, firma, vorname, nachname, strasse_nr, plz, ort, email, telefon, ust_id, hrb_gericht, hrb_nr)
VALUES (
  70, Verbraucher, , Vorname_anders, Nachname, Straße, 12345, Ort, email@email.de, +123456789, , , )
ON DUPLICATE KEY UPDATE art = Verbraucher, firma = , vorname = Vorname_anders, nachname = Nachname, strasse_nr = Straße, plz = 12345, ort = Ort, email = email@email.de, telefon = +123456789, ust_id = , hrb_gericht = , hrb_nr = ;

SET @kunden_id = LAST_INSERT_ID();
SELECT @kunden_id;

The primary key 70 does exist, therefore the query updates a row.

When I am running this query through Retool it returns 0 as the last id however when I am running the same query through MySQL Workbench it returns 70 as the last id.

Anyone know what the issue is?

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