How to get ID of the last updated row in MySQL

Question

How do I get the ID of the last updated row in MySQL using PHP

User · Answer

ID of the last updated row is the same ID that you use in the 'updateQuery' to found & update that row. So, just save(call) that ID on anyway you want.

last_insert_id() depends of the AUTO_INCREMENT, but the last updated ID not.

User · Answer

Further more to the Above Accepted Answer For those who were wondering about     amp     Significant difference between    and    and that is that    works as a variable-assignment operator everywhere  while   only works that way in SET statements  and is a comparison operator everywhere else     So SELECT  var   1   1  will leave  var unchanged and return a boolean  1 or 0 depending on the current value of  var   while SELECT  var    1   1  will change  var to 2  and return 2   Source

User · Answer

Hm  I am surprised that among the answers I do not see the easiest solution   Suppose  item id is an integer identity column in items table and you update rows with the following statement   UPDATE items SET qwe    qwe  WHERE asd    asd     Then  to know the latest affected row right after the statement  you should slightly update the statement into the following   UPDATE items SET qwe    qwe       item id LAST INSERT ID item id  WHERE asd    asd   SELECT LAST INSERT ID      If you need to update only really changed row  you would need to add a conditional update of the item id through the LAST INSERT ID checking if the data is going to change in the row

User · Answer

If you are only doing insertions  and want one from the same session  do as per peirix s answer  If you are doing modifications  you will need to modify your database schema to store which entry was most recently updated   If you want the id from the last modification  which may have been from a different session  i e  not the one that was just done by the PHP code running at present  but one done in response to a different request   you can add a TIMESTAMP column to your table called last modified  see http   dev mysql com doc refman 5 1 en datetime html for information   and then when you update  set last modified CURRENT TIME   Having set this  you can then use a query like    SELECT id FROM table ORDER BY last modified DESC LIMIT 1  to get the most recently modified row

User · Answer

This is officially simple but remarkably counter-intuitive  If you re doing   update users set status    processing  where status    pending  limit 1   Change it to this   update users set status    processing  where status    pending  and last insert id user id   limit 1   The addition of last insert id user id  in the where clause is telling MySQL to set its internal variable to the ID of the found row  When you pass a value to last insert id expr  like this  it ends up returning that value  which in the case of IDs like here is always a positive integer and therefore always evaluates to true  never interfering with the where clause  This only works if some row was actually found  so remember to check affected rows  You can then get the ID in multiple ways   MySQL last insert id       You can generate sequences without calling LAST INSERT ID    but the   utility of using the function this way is that the ID value is   maintained in the server as the last automatically generated value  It   is multi-user safe because multiple clients can issue the UPDATE   statement and get their own sequence value with the SELECT statement    or mysql insert id     without affecting or being affected by other   clients that generate their own sequence values    MySQL mysql insert id       Returns the value generated for an AUTO INCREMENT column by the   previous INSERT or UPDATE statement  Use this function after you have   performed an INSERT statement into a table that contains an   AUTO INCREMENT field  or have used INSERT or UPDATE to set a column   value with LAST INSERT ID expr        The reason for the differences between LAST INSERT ID   and   mysql insert id   is that LAST INSERT ID   is made easy to use in   scripts while mysql insert id   tries to provide more exact   information about what happens to the AUTO INCREMENT column    PHP mysqli insert id       Performing an INSERT or UPDATE statement using the LAST INSERT ID     function will also modify the value returned by the mysqli insert id     function    Putting it all together    affected rows   DB  getAffectedRows       update users set status    processing       where status    pending  and last insert id user id       limit 1     if   affected rows         user id   DB  getInsertId         FYI that DB class is here

User · Answer

My solution is   first decide the  id     uids    with select command and after update this id with  uids    SET  uids     SELECT id FROM table WHERE some   0 LIMIT 1   UPDATE table SET col   1 WHERE id    uids SELECT  uids    it worked on my project

User · Answer

Query     sqlQuery    UPDATE              update table          SET              set name    value           WHERE              where name    name          LIMIT 1      PHP function   function updateAndGetId  sqlQuery        mysql query str replace  SET    SET id   LAST INSERT ID id      sqlQuery        return mysql insert id        It s work for me

User · Answer

Hey  I just needed such a trick - I solved it in a different way  maybe it ll work for you  Note this is not a scalable solution and will be very bad for large data sets   Split your query into two parts -   first  select the ids of the rows you want to update and store them in a temporary table    secondly  do the original update with the condition in the update statement changed to where id in temp table   And to ensure concurrency  you need to lock the table before this two steps and then release the lock at the end   Again  this works for me  for a query which ends with limit 1  so I don t even use a temp table  but instead simply a variable to store the result of the first select    I prefer this method since I know I will always update only one row  and the code is straightforward

User · Answer

I ve found an answer to this problem     SET  update id    0  UPDATE some table SET column name    value   id    SELECT  update id    id  WHERE some other column    blah  LIMIT 1   SELECT  update id    EDIT by aefxx  This technique can be further expanded to retrieve the ID of every row affected by an update statement   SET  uids    null  UPDATE footable    SET foo    bar   WHERE fooid  gt  5    AND   SELECT  uids    CONCAT WS      fooid   uids     SELECT  uids    This will return a string with all the IDs concatenated by a comma

User · Answer

SET  uids        UPDATE myf   ingtable    SET id   id    WHERE id  lt  5   AND   SELECT  uids    CONCAT WS      CAST id AS CHAR CHARACTER SET utf8    uids     SELECT  uids    I had to CAST the id  dunno why     or I cannot get the  uids content  it was a blob  Btw many thanks for Pomyk answer

User · Answer

This is the same method as Salman A s answer  but here s the code you actually need to do it   First  edit your table so that it will automatically keep track of whenever a row is modified  Remove the last line if you only want to know when a row was initially inserted   ALTER TABLE mytable ADD lastmodified TIMESTAMP      DEFAULT CURRENT TIMESTAMP      ON UPDATE CURRENT TIMESTAMP    Then  to find out the last updated row  you can use this code   SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1    This code is all lifted from MySQL vs PostgreSQL  Adding a  Last Modified Time  Column to a Table and MySQL Manual  Sorting Rows  I just assembled it

User · Answer

No need for so long Mysql code  In PHP  query should look something like this    updateQuery   mysql query  UPDATE table name SET row  value  WHERE id   id    or die   Error     lastUpdatedId   mysql insert id

[php] How to get ID of the last updated row in MySQL?

Examples related to php

Examples related to mysql