Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Move rows between tables in SQL

I have 2 tables, an active table and an inactive table. I want to move rows from the active to the inactive table. My first thought was

insert into inactive select * from active where ...
delete from active active where ...

However about .42 seconds later I noticed this will drop/duplicate rows if updates alter what the where clause selects.

In this case, I can easily prevent that but what should I do in cases where I can't?

edit: From the answers it look like there isn't an easy/trivial way to do this. I'm really surprised by this. I would think that there would be some substantial benefits to having it.

like image 464
BCS Avatar asked Nov 18 '08 20:11

BCS


People also ask

How do I move a row from one table to another in SQL?

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.

How do I move data from one database table to another database in SQL?

Enter the data source, server name and select the authentication method and the source database. Click on Next. Now, enter the destination, server name, authentication method and destination database then click on Next. Select 'Copy data from one or more tables or views' option in the next window and click on Next.


1 Answers

Status flags are your friend.

UPDATE old_data SET move="MARKED";
INSERT INTO somewhere... SELECT where move="MARKED";
DELETE FROM old_data WHERE move="MARKED";

If you do this with Autocommit off, it will seize locks all over the place.

You can COMMIT after each step, if you want to do a little less locking.

like image 62
S.Lott Avatar answered Oct 14 '22 09:10

S.Lott