I have 2 tables, table1 and table2.
I have a row in table1 and want to move it to table2 and remove it from table1. Basically, a cut+paste.
I am using php. My current plan is to select from table1, store data in php, insert into table2, then delete from table1. This seems like a very long process.
Is this really the best way to do this?
You can move rows from one table to another with the help of INSERT INTO SELECT statement.
The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
To copy column definitions from one table to another. Open the table with columns you want to copy and the one you want to copy into by right-clicking the tables, and then clicking Design. Click the tab for the table with the columns you want to copy and select those columns. From the Edit menu, click Copy.
You're going to need at least 2 queries:
INSERT INTO table2 (column_name1, column_name2) SELECT column_name1, column_name2 FROM table 1 WHERE <insert_where_clause_here> DELETE FROM table1 WHERE <your_where_clause>
I see no shorter way of doing this using MySQL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With