Mysql returning clause equivalent

Question

I am a complete newbie to MySql so please be gentle.

Is there an equivalent of the RETURNING clause in Oracle or the Inserted'/'Deleted tables in SQL Server in MySQL? What I want to do is the following:

Delete a set of rows from table A
Insert the deleted set of rows into table B.

Please help!

Thanks

@Korhan, Not in a trigger, but within an SP. I know within a trigger you can get old/new. But I need this within an SP — SimpleUser, Mar 06 '12 at 12:11
You **cannot** do both insertion and deletion in a single query @Cpat. Check my answer below... — Korhan Ozturk, Mar 06 '12 at 12:27

Korhan Ozturk · Accepted Answer · 2012-03-06T12:26:37.643

11

Unfortunately, you can't do both insertion and deletion in one query, but you can do it all in one transaction if you are using a transactional store engine (like InnoDB). Moreover, RETURNING is supported by Oracle and PostgreSQL but not by MySQL and therefore you need to write separate delete and insert statements.

Using a transaction however, will guarantee that only the successfully copied data will be deleted from tableA. Consider the following:

begin transaction;
insert into tableB select * from tableA where 'your_condition_here';
delete from tableA where 'your_condition_here';
commit;

edited Mar 06 '12 at 12:26

answered Mar 06 '12 at 12:18

Korhan Ozturk

11,148
6
36
49

That is what I am finding as well. NO equivalent clause in MySql for what I want to do. Oh well, 2 separate statements is it then. thank you for your help – SimpleUser Mar 06 '12 at 12:28
Thank you for verifying my answer. I'm glad that my answer was helpful. – Korhan Ozturk Mar 06 '12 at 12:31
5

Note `insert ... returning` is supported in MariaDB 10.5 and newer; but not Oracle MySQL AFAIK. https://mariadb.com/kb/en/insertreturning/ – Martin Tournoij Dec 15 '21 at 13:23

score 0 · Answer 2 · answered Mar 06 '12 at 12:14

0

Why not insert the rows to be deleted from table A in table B and then delete the rows from table A? you can achieve that like this:

insert into tableB select * from tableA where condition;

and then

delete from tableA where condition.

answered Mar 06 '12 at 12:14

Euclides Mulémbwè

1,677
11
18

1

Ideally I'd like to just run the one statement. something similar to what you do in Sql Server -> Delete from table1 output deleted.col1, deleted.col2 into table2(col3, col4); – SimpleUser Mar 06 '12 at 12:18

Mysql returning clause equivalent

2 Answers2

Linked

Related