How Can I Copy Data Between MySQL Tables with Different Field Structures?

Copying Data Between Tables in MySQL

Problem:
How can one transfer data from an existing table (referred to as Table 1 henceforth) to a newly created table (Table 2) in MySQL? Table 1 contains specific fields, while Table 2 is designed with a different set of fields.

Solution:

To fulfill this requirement, MySQL provides a method to insert data into a table by selecting from another table using the INSERT INTO syntax. This technique can be employed to selectively copy fields from Table 1 into Table 2.

The following query effectively accomplishes the task:

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

This query operates as follows:

• The target table, table2, is specified in the INSERT INTO statement.
• The fields within table2 that will receive the data are listed in the parentheses.
• The SELECT statement defines the data to be copied from Table 1.
• The columns from Table 1 are mapped to the corresponding fields in Table 2. In this case, st_id, from_uid, the current timestamp (now()), 'Pending', and 'Assigned' are being copied to table2.
• If the entire dataset from Table 1 is to be copied, a semicolon (;) should be used to terminate the query. Alternatively, a WHERE clause can be added to the SELECT statement to select a specific subset of rows for the copy operation.

The above is the detailed content of How Can I Copy Data Between MySQL Tables with Different Field Structures?. For more information, please follow other related articles on the PHP Chinese website!