LAST_INSERT_ID not working on UPDATE

Question

if I use this SQL:

UPDATE formulare SET EV_id=59, EV_status=5 WHERE EV_id=57 AND ID_uziv=12;SELECT LAST_INSERT_ID();

I will get 0 as last insert id. I'm using php mysqli_insert_id and here is said that:

The mysqli_insert_id() function returns the ID generated by a query 
on a table with a column having the AUTO_INCREMENT attribute. 
If the last query wasn't an INSERT or UPDATE statement 
or if the modified table does not have a column with the AUTO_INCREMENT attribute, 
this function will return zero.

my table formualre has auto increment column, so I don't know wher the problem is

update will not give you the last insert id.It only gives you the count of records updated.Last insert id is only for insert — 웃웃웃웃웃, Aug 30 '13 at 10:37
`last_` **insert** `_id` should tell you anything you need to know. There was nothing inserted. So there is no ID returned. — Tobias Golbs, Aug 30 '13 at 10:38
documentation [here](http://php.net/manual/en/mysqli.insert-id.php) and my response to your answers is under the @dystroy answer — koubin, Aug 30 '13 at 10:49

score 8 · Accepted Answer · edited May 31 '21 at 01:34

8

LAST_INSERT_ID() won't work if no new auto increment value was created.

The solution is something like this:

UPDATE formulare
  SET EV_id=LAST_INSERT_ID(59),
    EV_status=5
  WHERE EV_id=57
    AND ID_uziv=12;
SELECT LAST_INSERT_ID();

Note: I guess, that EV_id is the auto_increment primary key.

Otherwise you should do a query like:

UPDATE formulare
  SET key_col = LAST_INSERT_ID(key_col),
    EV_id=59,
    EV_status=5
  WHERE EV_id=57
    AND ID_uziv=12;
SELECT LAST_INSERT_ID();

edited May 31 '21 at 01:34

Regular Jo

5,190
3
25
47

answered Aug 30 '13 at 10:43

Jakub Matczak

15,341
5
46
64

2

It's also very helpful while doing `INSERT ... ON DUPLICATE KEY UPDATE` query to get `id` when row is updated. Even documentation mentions about this trick. :) – Jakub Matczak Aug 30 '13 at 12:03

score 2 · Answer 2 · answered Aug 30 '13 at 10:39

2

From the documentation :

The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.

As your update didn't create a new record, it didn't generate any AUTO_INCREMENT value.

answered Aug 30 '13 at 10:39

Denys Séguret

372,613
87
782
758

ok, but why there is this `...If the last query wasn't an INSERT or UPDATE statement...` ? – koubin Aug 30 '13 at 10:45
1

This sentence is curious, but maybe the author was referring to [this kind of statement](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html). – Denys Séguret Aug 30 '13 at 11:14

score 1 · Answer 3 · answered Aug 30 '13 at 10:45

UPDATE query updates the existing record, it doesn't return any new ID.

mysqli_insert_id retrieves the ID generated for an AUTO_INCREMENT column by the        
previous query (usually INSERT).

There was no INSERT query, that's the reason, you won't get any Id after executing UPDATE query.

For more info, refer mysql_insert_id

LAST_INSERT_ID not working on UPDATE

3 Answers3

Linked