Geek Answers Handbook

MySQL: copying an entire row from one to another and deleting the original

Can someone explain (or point in the right direction) how I will move several rows from one table to another and delete a row from the original table based on a given criterion?

I understand

INSERT INTO table2 SELECT * FROM table1

to copy data from one table to another, but I need to delete the original. The reason is that it was suggested to speed up the query of the table, I have to move all redundant data (completed, expired, products older than 3 months) from the main table to another.

A bit of background, I have a table in which the products are stored, some products have expired, but the products should still be available. There are about 50,000 products that have expired and 2,000 are active. There is a status column (int 1 = active, 2 = expired, etc.) to determine what to show on the front panel.

I think this post is 2 questions:

  • Is there a better way to speed up querying a product table without removing obsolete items?
  • If not, how to move rows from one table to another

Thank you very much!

+4
source share
2 answers

INSERT INTO table2 (column_name1, column_name2) SELECT column_name1, column_name2 FROM table 1 WHERE (here is a sentence)

DELETE FROM table1 WHERE (here is a sentence)

Source above: mysql moves a row between tables

+6
source

50,000 entries in the table are really not many. If you have performance issues, I would look at your queries and your indexes to speed things up. And since these expired records should still be available, then it can be harder to have multiple tables to support.

However, to move data from one table to another, as you requested, you just need to run 2 different statements. Assuming you want to move inactive products:

 INSERT INTO ProductsBackup SELECT * FROM Products WHERE Status <> 1 DELETE FROM Products WHERE WHERE Status <> 1

If you have identifiers in your columns, you might be better off providing column names. But assuming ProductId is Identity, then be careful moving them to another table, since you probably don't want to lose this original identifier, as it may point to other tables.

Good luck.

+4
source

More articles:


All Articles