MySQL query results copied to new table (update, insert)

MySQL query results copied to new table (UPDATE, insert):

MySQL can copy query results to another table, there are usually two cases of replication, one is to update the existing data, the other is to insert a new record. This is illustrated by an example below. Build two test tables first.

Table T1:

Table T2:

1. If the score value exists in the T2 table, the score is updated to the T1 table. Here's how:

UPDATE t1,t2 SET t1.score = t2.score WHERE t1.id = t2.id and T2.score is not NULL

This is to update the query result as a condition for another table, of course, T2 can also be a more complex query result rather than a specific table.

2. Update the username of the T1 table to the T2 table, and update the score of the T2 table to the T1 table. Here's how:

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, can update the data of two tables at the same time, that is, the table part of the data replication, update.

3. Insert the query results of the T2 table into the T1 table. Here's how:

INSERT into T1 (id,username,score) SELECT t2.id,t2.username,t2.score from t2 where t2.username = ' Lucy '

The first two ways to update a table's records is to insert a new record. In fact, as can be seen from the script, this method is to merge the query and insert two steps into one.

MySQL query results copied to new table (update, insert)