37190

How to get the primary key of last updated record in MYSQL?

Question:

We know if the primary key is autocrement, SELECT statement could return what I need. What about UPDATE?

Using last_insert_id is thread safe?

Answer1:

<blockquote>

Using last_insert_id is thread safe?

</blockquote>

It's <em>connection</em>-safe, which is probably what you mean. From <a href="http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id" rel="nofollow">the docs</a>:

<blockquote>

The ID that was generated is maintained in the server on a <em>per-connection basis</em>. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column <em>by that client</em>. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

</blockquote>

(Their emphasis.)

Answer2:

I don't think last_insert_id will help you. Your best bet is to make a select statement that is the same as your update, and parse the results.

Answer3:

For this you create a new table actiontrack and if update query is run then insert a new record in this table

and then fetch a record in this action table using action id desc

or

take a timestamp field in the table and if update query is run then also update the timestamp field

then fetch the latest new timestamp field

Answer4:

You cannot achieve that unless you have a MODIFIED_DATE sort of column that you revisit in every <em>update</em> of the record to keep track of your last modification date in order to <em>order</em> on that.

Answer5:

first thing you have to do two operations in order to achieve this.

Best solution I can think as of now, a) select PK from table with same where condition which you are going to update. b) update records with PK returned.

All other solution may fail at some point.

Answer6:

last_insert_id() does not return the id of the recently updated row, it only returns the id of the last inserted row

With that your option would be to select it first and if it doesnt exist insert it and you can use last_insert_id() , if it exists update it using the id returned from your first select

Recommend

  • How can I create a secured connection to BLE peripheral through CBCentralManager?
  • Conflict of variables in R language
  • Bug in Safari 9? adding blue color to links' border
  • Multiple languages in one HTML page
  • sql join on two fields in one table
  • Getting double value from the quotient of integers
  • mysql not unique auto increment, primary key two fields
  • Using ui-router for “main” layout?
  • 2 count(*)+group by+having+join
  • In a django custom field, what does the SubfieldBase metaclass do?
  • Launch app on startup for all users, but also allow per-user setting (Windows)
  • PostgreSQL primary key auto increment crashes in C++
  • How to find data from last week in MySQL
  • What does Main.1 output file do in XCode?
  • text-align justify, cannot override
  • Less Conflicting Session Manager for Zope 2
  • Request response issues in biztalk
  • Compare struct to a constant in C
  • Getting unused unique values on a SQL table
  • Doctrine2 bulk import try to work with another entity
  • Wrong labels when plotting a time series pandas dataframe with matplotlib
  • what makes a request a new request in asp.net C#
  • SetWindowsHookEx does not react on media keys
  • Android application: how to use the camera and grab the image bytes?
  • Spring Cloud Microservice Architecture Confusion
  • Transactional Create with Validation in ServiceStack Redis Client
  • Chrome doesn't support silverlight anymore? How to solve this?
  • Handling un-mapped Rest path
  • FB SDK and cURL: Unknown SSL protocol error in connection to graph.facebook.com:443
  • QLineEdit password safety
  • Nant, Vault & Windows Integrated Authentication
  • PHP - How to update data to MySQL when click a radio button
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Join two tables and save into third-sql
  • Convert array of 8 bytes to signed long in C++
  • Understanding cpu registers
  • Why joiner is not used after Sequence generator or Update statergy
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • Android Heatmap on canvas or ImageView