只需将行移动到另一个表并返回

Question

Is there an easy way to SELECT a row and INSERT it into another table that has exactly the same columns? It needs to reserve the id and be able to move it back later on.

STEPS:

1. Move entire row to an identical temporary table (with a different name of-course) while using the same id.

2. Delete the row from old table (I know this, dont worry ;))

3. Move the row back from the temporary table into the original table, while using the same original id.

4. Delete row from temporary table.

I know how to do this, but there are probably much better, cleaner and faster ways to achieve this in one or two queries. My main concern is that when the table gets refactored, the query should still do its job without skipping newer columns.

I hope someone can come with a good suggestion :)

Answer 1

Assuming the tables have identical columns, you should be able to just do this:

INSERT INTO newtable SELECT * FROM oldtable WHERE id = somevalue