lastInsertId() for UPDATE in Prepared Statement

$query = $database->prepare("UPDATE table SET value = value WHERE value = '$value'");
$query_1->execute();
$database->connection->lastInsertId();

The code above does not work. The value I am trying to get is primary key auto increment.

As lastInsertId() suggests by its name, it may be intended for insert only. If so, what is the proper way to achieve the same goal on an UPDATE?

The value I wish to return is the primary key of the table, if the update has occurred.

Something like..

lastUpadteId()



  $query_1 = $database->connection->prepare("UPDATE accounts SET traffic = traffic + '$file_size', id = LAST_INSERT_ID(id) WHERE primary_key = '$account_id'");
  $query_1->execute();
  echo $database->connection->lastInsertId();

Throws…

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘id’ in ‘field list” in

Answer

The LAST_INSERT_ID() doesn’t work on UPDATE as it works on INSERT. But you can still get the id of the updated row using LAST_INSERT_ID() after an update. Here is the way your query should be written:

UPDATE table 
SET
  value = value, 
  primary_key = LAST_INSERT_ID(primary_key) 
WHERE value = 'some value'

Does it look strange for you primary_key = LAST_INSERT_ID(primary_key)? Then read this from MySQL Manual: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html