OUTPUT Clause in MySQL

Question

Is there a way to simulate the OUTPUT clause in MySQL, as we have a OUTPUT clause in SQL Server.

Here is the kind of query I have

UPDATE       employee SET          empage = 10 OUTPUT       INSERTED.empid WHERE        (empage < 10) 

As I need to have this functionality for MySQL server database too.

Kindly suggest the best way to achieve this functionality.

Answer 1

1. You could create a trigger and insert values you need into another table.
2. I'm not sure, but - for MYISAM tables you could lock employee table, select and insert values into another table, and then update and unlock employee table.

EDIT:

I have tried one scenario with InnoDb table, seems it works -

START TRANSACTION;  SELECT * FROM table WHERE id = 1 FOR UPDATE; -- lock rows -- Or call this select to insert and lock rows -- INSERT INTO table_output SELECT * FROM table WHERE id = 1 FOR UPDATE;  -- Make modifications UPDATE table SET column1 = '111' WHERE id = 1;  COMMIT; 

SELECT statement (FOR UPDATE clause)