Mysql query results copied to the new table method (update, insert) _mysql

MySQL can copy query results to another table, there are usually two cases of replication, one is to update existing data, the other is to insert a new record. Here is an example to illustrate. First build two test tables.

Table T1:

Table T2:

1. If the score value exists in the T2 table, the score is updated to the T1 table. 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 the query results as a condition for another table, and of course, T2 can also be a more complex query result than a specific table.

2. Update the username of the T1 table to the T2 table, and update the T2 table score to the T1 table. 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 two-table data, that is, the table part of the data replication, update.

3, the query results of the T2 table into the T1 table. 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 ways to update a table's records is to insert a new record. In fact, as you can see from the script, this method is to combine the query and insert two steps into one.

The above is small series for everyone to bring the MySQL query results copied to the new table method (update, insert) all the content, I hope that we support cloud Habitat Community ~