Geek Answers Handbook

Is it possible to move a record from one table to another using a single SQL statement?

Do I need a query to move a record from one table to another without using multiple statements?

+4
source share
5 answers

If you really want to do this in a single SQL statement, one way to achieve this is to create an “after delete” trigger on the source table that inserts the row into the target table. This way you can move the row from the source table to the target table by simply deleting it from the source table. Of course, this will only work if you want to insert into the goal table for each deletion in the source table.

DELIMITER $$ DROP TRIGGER IF EXISTS TR_A_DEL_SOURCE_TABLE $$ CREATE TRIGGER TR_A_DEL_SOURCE_TABLE AFTER DELETE ON SOURCE_TABLE FOR EACH ROW BEGIN INSERT IGNORE INTO TARGET_TABLE(id,val1,val2) VALUES(old.id,old.va1,old.val2); END $$ DELIMITER ;

So, to move the row with id 42 from the source table to the target table:

 delete from source_table where id = 42;
+10
source

No, you cannot move records in a single SQL statement. You must use an INSERT followed by a DELETE . You must wrap these instructions in a transaction to ensure that the copy operation remains atomic.

 START TRANSACTION; INSERT INTO new_table SELECT * FROM old_table WHERE some_field = 'your_criteria'; DELETE FROM old_table WHERE some_field = 'your_criteria'; COMMIT;
+16
source

No - you could do INSERT in one subdocument, but you still need to delete the entry.

+5
source

It is not possible to make one request, but if you want to do this in one request in the application, you can create a stored procedure to do this for you.

 DELIMITER $$ DROP PROCEDURE IF EXISTS `copydelete` $$ CREATE PROCEDURE `copydelete` (id INT) BEGIN INSERT INTO New_Table SELECT * from Old_Table WHERE Old_Table.IdField=id; DELETE FROM Old_Table where IdField=id; END $$ DELIMITER ;

Then you new request just

 CALL copydelete(4);

WHERE IdField = 4 will be deleted;

+2
source

Please note that the time delay between insert-select and deletion can result in significant deletion.

For a safe route, you can use the update field:

 update old_table set move_flag=1 where your_criteria insert into ... select from ... where move_flag = 1 delete from old_table where move_flag=1

Or use a transaction that locks old_table, so no data can be added between insert ... select and delete.

0
source

More articles:


All Articles