Search code examples
mysqlsqlinsertlast-modified

How to return all last row's fields of the last Inserted/updated row?

As from title, I would like to return last row inserted/updated fields and values, is it possible in the same INSERT and the UPDATE query?

Solution

  • If you use primary key, you can use LAST_INSERT_ID(); for example:

    insert into user(name) values ('your name');
select * from user where id = LAST_INSERT_ID();

    If you need to get all row recently inserted or updated, I sugest to add INSERTED and UPDATED field with date time data type. So you can select your table with recently interval. See sample bellow:

    SELECT *, INSERTED, UPDATED
FROM YOUR_TABLE 
WHERE INSERTED BETWEEN (NOW() - INTERVAL 1 MINUTE) AND NOW() 
   OR UPDATED BETWEEN (NOW() - INTERVAL 1 MINUTE) AND NOW();

    this will return last one minute modified or inserted record.