How Can I Selectively Copy Data Between MySQL Tables Using INSERT INTO...SELECT?

Copying Data Between MySQL Tables with Custom Field Selection

In the realm of database management, efficiently transferring data between tables is a common task. MySQL provides several methods to accomplish this, including the INSERT INTO...SELECT statement.

Consider a scenario where you have two MySQL tables, Table 1 (the existing table) and Table 2 (the new table). You wish to selectively copy specific fields from Table 1 into Table 2, based on the following criteria:

• Table 1: Contains columns such as aid, st_id, from_uid, and message.
• Table 2: Contains columns such as st_id, uid, changed, status, and assign_status.

Using MySQL Queries for Selective Data Copy:

To achieve your goal, you can employ the following MySQL query:

INSERT INTO table2 (st_id, uid, changed, status, assign_status)
SELECT st_id, from_uid, now(), 'Pending', 'Assigned'
FROM table1;

This query accomplishes the following:

1. Inserts data into Table 2, including the following columns:

   • st_id
   • uid
   • changed (set to the current timestamp using the NOW() function)
   • status (set to 'Pending')
   • assign_status (set to 'Assigned')

2. Selects data from Table 1, matching the columns in the INSERT statement. Specifically, it selects:

   • st_id
   • from_uid

If you wish to copy all rows from Table 1, you can omit the WHERE clause. However, if you want to limit the copy to a specific subset of rows, you can add a WHERE clause with the desired conditions.

The above is the detailed content of How Can I Selectively Copy Data Between MySQL Tables Using INSERT INTO...SELECT?. For more information, please follow other related articles on the PHP Chinese website!