CODEWITHSUNDEEP
phpmysql
Gilly
Gilly

Reputation: 9692

Simply move row to another table and back

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 :)

Upvotes: 1

Views: 1045

Answers (4)

DiMono
DiMono

Reputation: 3368

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

INSERT INTO newtable SELECT * FROM oldtable WHERE id = somevalue

Upvotes: 4

K. Shahzad
K. Shahzad

Reputation: 87

You can do this :

INSERT INTO tbltemp (Select * from table1 where someid=somevalue)

Or you could move the entire table into other one :

INSERT INTO tbltemp (select * from table1);

What ever you do, be careful that the record/records which you need to move/copy must not include any primary key values which are already there in target table.

Upvotes: 0

Praveen Lobo
Praveen Lobo

Reputation: 7187

You use CREATE TABLE ... SELECT to create a table and then work with it. This will create a table with columns from SELECT in your case the original table. Then you can delete the rows from original table, move the rows back to the original table using INSERT INTO ... SELECT followed by dropping the temporary table. This way you don't need to maintain two tables and the temporary table will always get the latest columns from the original table.

See mysql manual. Following example copied from manual. 

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

So you need something like this 

CREATE TABLE bar SELECT * FROM foo WHERE fooColumn = 'whatever';
DELETE FROM foo where fooColumn <> 'whatever';
INSERT INTO foo SELECT * FROM bar;
DROP TABLE bar

if you want to avoid creating and dropping tables too often, just create the temporary table once. 

INSERT INTO bar SELECT * FROM foo WHERE fooColumn = 'whatever';
DELETE FROM foo where fooColumn <> 'whatever';
INSERT INTO foo SELECT * FROM bar;

But make sure you keep the temporary and the original table in sync all the time i.e. if you add or drop a column in the original table, make sure that is replicated in the temporary table immediately as INSERT INTO ... SELECT only copies data not schema.

Upvotes: 1

Rhys
Rhys

Reputation: 13

Here are two alternate ideas I can think of:

  1. Add a BIT or BOOL column to the table that can be updated in order to 'flag' the record one way or another so that you can filter on it in your queries.
  2. Create a table that just lists the ID of the record(s) that you want to flag. Enter a new ID into this field when you want it flagged (replaces the act of deleting and moving to the 2nd table) and remove it when you wish to 'unflag' it (replaces the act of deleting it from the 2nd table and re-adding it to the original table.

Aside from the initial schema change required by option 1, both options will work regardless of any changes to the schema of the original table. If you are trying to avoid maintaining the second table, I'd consider these.

If you do wish to copy to the holding table and delete from the original table, and later copy back to the original table, and delete from the holding table, be sure to wrap those operations into a transaction so that you don't end up with duplicated data or data loss in the event of things not going as planned.

Upvotes: 1

Related Questions