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

Question

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

Answer 1

No, you cannot move records in one SQL statement. You have to use an INSERT followed by a DELETE statement. You should wrap these statements into a transaction, to make sure 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;