The method for copying MySQL query results to a new table (update and insert) and mysql query results

The method for copying MySQL query results to a new table (update and insert) and mysql query results

In MySQL, you can copy the query results to another table. there are usually two types of replication: update existing data and insert a new record. The following is an example. First, construct two test tables.

Table t1:

Table t2:

1. If score Values exist in Table t2, update the score to table t1. The method is as follows:

UPDATE t1, t2
SET t1.score = t2.score
WHERE t1.id = t2.id AND t2.score IS NOT NULL

This is to update another table using the query result as a condition. Of course, t2 can also be a more complex query result rather than a specific table.

2. Update the username of table t1 to table t2 and the score of table t2 to table t1. The method is as follows:

UPDATE t1, t2
SET t1.score = t2.score, t2.username = t1.username
WHERE t1.id = t2.id

This method is similar to the above method. You can update the data of two tables at the same time, that is, copying and updating some of the table data.

3. Insert the query results of table t2 to table t1. The method is as follows:

Insert into t1 (id, username, score)
SELECT t2.id, t2.username, t2.score FROM t2 where t2.username = 'Lucy'

The first two methods are to update the table record, which is to insert a new record. In fact, we can see from the script that this method is to combine the query and insert steps into one.

The preceding section describes how to copy MySQL query results to a new table (update and insert ~