How to Get Id of the Last Updated Row in MySQL

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

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 > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

This will return a string with all the IDs concatenated by a comma.

How to get ID of updated row?

The documentation for insert_id clearly states:

Returns the ID generated by an INSERT or UPDATE query on a table with a column having the AUTO_INCREMENT attribute.

Your query does not generate a new ID. You can't use $db->insert_id as there was no new ID reported by MySQL server.

You can trick MySQL into providing this value. Just reset the ID to the value that it had previously by regenerating it again.

$sql = $db->prepare("UPDATE `timeslots` 
    SET `service` = ?, Id=LAST_INSERT_ID(Id) 
    WHERE `status` = ?");

See How to get ID of the last updated row in MySQL?

Get id of last updated row mysql in vb.net

add your table a new column with DATETIME type. and choose on update CURRENT timestamp option from attribute section. In this way whenever an update occurs in the column your database will update this row automaticly. Then all yo need to do is writing your SQL command from your script.

SELECT id fROM table  ORDER BY name_of_datetime_column DESC LIMIT 1

will return only one column which is last updated one

Last Updated row ID in Mysql

When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID() statement with mysql_query() and retrieving the value from the result set returned by the statement.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

Read more here.

How does the trick 'get ID of the last updated row in MySQL' work?

If you initialize @uids to an empty string, then CONCAT_WS(',', fooid, @uids) will result in fooid, instead of just fooid when it does the first concatenation, because it will concatenate fooid and '' with a comma between them. But when any of the arguments to CONCAT_WS() is NULL, it's ignored completely and no comma is put between it and the adjacent elements.

It only selects the affected rows because AND performs short-circuiting. This means that the parameters are evaluated from left to right, and if the first parameter is FALSE it doesn't evaluate the second parameter at all. So it only executes the @uids := ... assignment when fooid > 5 is TRUE.

If you have a LIMIT clause, the behavior depends on whether the column you're ordering by is indexed. If it is, it will scan the index in order, and then evaluate the WHERE clause, and stop when the limit count is reached, so only the rows within the limit will be included in @uids. Or if you have no ORDER BY clause, it just scans the table until the limit is reached.

However, you can't depend on this reliably, it depends on how the query optimizer analyzes the query.

But if the ordering column is not indexed, or the query optimizer isn't able to perform the above optimization, it may have to scan the entire table, selecting all the rows that match the WHERE clause into a temporary table, and then perform the ordering on that. In that case, @uids will contain all the matching IDs, not just the ones within the limit. You can solve this by using a subquery.

SET @uids := null;
UPDATE footable AS t1
JOIN (SELECT fooid
      FROM footable
      WHERE fooid > 5
      ORDER BY somecolumn
      LIMIT 10) AS t2
ON t1.fooid = t2.fooid
SET t1.foo = 'bar'
WHERE @uids := CONCAT_WS(',', fooid, @uids);
SELECT @uids;

Get the id of the last updated record

Codeigniter doesn't support that. I had to do this:

$updated_id = 0;

// get the record that you want to update
$this->db->where(array('vrnoa'=>$data['vrnoa'], 'etype' => 'sale'));
$query = $this->db->get('StockMain');

// getting the Id
$result = $query->result_array();
$updated_id = $result[0]['stid'];

// updating the record
$this->db->where(array('vrnoa'=>$data['vrnoa'], 'etype' => 'sale'));
$this->db->update('StockMain',$data);

How can i return the id of the last updated record in MySQL?

I count not find a way to return last updated id. But what I have done is to accomplish what I am looking for is by checking the countRow method as it return the total effected rows.

So I do an update if a record is found then done. if there was no record found I do an insert.

Thanks

---

Related Topics