Edit the latest row in the database?

Question

How can I edit the latest row in the database. I only know it's the last one. I don't know its id.

Answer 1

For a table with an auto_increment id field:

UPDATE tbl SET col1 = 'val1' WHERE id = MAX(id);

Answer 2

I don't know which language you are working with, in PHP's mySQL functions you can use

mysql_insert_id()

there are similar function in every other mySQL client library I know of.

Also, there is a native mySQL function!

LAST_INSERT_ID() (with no argument) returns the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

mysql> SELECT LAST_INSERT_ID(); -> 195

Of course, a primary key with AUTO_INCREMENT is required for these functions to work.

Answer 3

If it's a row that has been inserted in your script (the same script from which you want to update it) and there is an auto_increment column on your table, you can get that auto_increment value, using functions such as those, for PHP :

• mysql_insert_id
• mysqli_insert_id
• PDO::lastInsertId

There should be an equivalent for probably any language you can possibly be using for your application.

If your are trying to do an update from another script than the one in which you did the insert, and still have an auto_increment column, the best way will probably be to update the row that has the biggest value for that column :

update your_table
set your_column = ...
where id = max(id)

Or, in two steps (not sure it'll work in one) :

select max(id) as id from your_table

update your_table set your_column = ... where id = [what you got with thr first query]

Answer 4

You can also use UPDATE table SET ... WHERE id=LAST_INSERT_ID() (supposing the last insert was on the table you want to query).

Answer 5

I would not use TWO steps to find the last insert ID simply because a new record could be added in the mean time.

Depending on your version, you should be able to call $handle->last_id(); or $handle->{mysql_insertid};

Chris