Is there an equivalent to lastInsertId () for an UPDATE?


I am using PHP and MySQL with PDO; I would like if there is an equivalent to the lastInsertId () method that returns the last ID of a row that has been inserted, but that returns the last ID of a row that was updated.

asked by Pit 25.05.2016 в 23:33

2 answers


In MySQL there is no native function that returns the ID (or IDs) of the last row (or rows) modified with a UPDATE in the database.

In the English version of StackOverflow Pomyk and aefxx suggest an interesting solution that works for both the last ID and group of IDs (if more than one row was modified):

SET @id_actualizado := 0;

UPDATE nombre_tabla 
SET    nombre_columna = 'valor', 
       id = (SELECT @id_actualizado := id)
WHERE  otra_columna = 'lo_que_sea' 

SELECT @id_actualizado;

That code will return the updated row ID. Now, if you want to return all the IDs of all the modified rows (if more than one) separated by commas, then it would be something like this ( id would be the primary key / ID of the table):

SET @uids := null;

UPDATE nombre_tabla
SET    nombre_columna = 'valor'
WHERE  otra_columna > 5 AND
       ( SELECT @uids := CONCAT_WS(',', id, @uids) );

SELECT @uids;
answered by 26.05.2016 / 05:17

try this

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection  
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";

if ($conn->query($sql) === TRUE) {
   //ultimo id
   $last_id = $conn->insert_id;
   echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;

answered by 26.05.2016 в 01:08