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?

<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.)

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.

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

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.

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.

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