25

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.

bobs
  • 21,844
  • 12
  • 67
  • 78
Saravanan
  • 7,637
  • 5
  • 41
  • 72

1 Answers1

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

Devart
  • 119,203
  • 23
  • 166
  • 186
  • I thought of having some clauses in the query that did this trick.. anyways.. let me check the possibilities – Saravanan Apr 29 '11 at 11:54
  • i am using InnoDB and donot want to create a trigger, as this needs control from my app. Is there a lock mechanism that i can use? – Saravanan Jun 29 '11 at 08:49
  • I have this query being answered in http://dba.stackexchange.com/questions/3555/query-regarding-combining-an-update-and-an-insert-query-into-a-single-query-in-my/3557. For your reference. – Saravanan Jul 01 '11 at 06:29