MySQL Two table has inconsistent field associated fields

Work encountered a problem, the mail system mass failure, and then after the investigation to find the cause

It turns out that the associated fields of two tables in MySQL are inconsistent,

Table A

mysql> desc Rm_user_router;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Corpid | Int (10) | NO | PRI | NULL | |
| LOGINNAME | varchar (60) | NO | PRI | NULL | |
| UIN | Int (10) | NO | MUL | NULL | |
| SPID | Int (10) | NO | | NULL | |
| Miid | Int (10) | NO | | NULL | |
| ASID | Int (10) | NO | | 0 | |
| FLAG | Int (1) | NO | | 0 | |
| Registerid | varchar (60) | NO | | NULL | |
| STATUS | Int (2) | NO | | NULL | |
| RES1 | Int (10) | NO | | 0 | |
| RES2 | Int (10) | NO | | 0 | |
| Createtime | datetime | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+

Table B

Mysql> desc Rm_group_info;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| UIN | Int (10) | NO | PRI | NULL | |
| Corpid | Int (10) | NO | MUL | NULL | |
| GROUPID | varchar (60) | NO | | NULL | |
| GROUPNAME | varchar (32) | YES | | NULL | |
| Maxmembernum | Int (10) | YES | | 0 | |
| Curmembernum | Int (10) | YES | | 0 | |
| STATUS | Int (2) | YES | | 0 | |
| FLAG | Int (2) | YES | | 0 | |
| Recvtype | Int (2) | YES | | 0 | |
| Createtime | datetime | NO | | NULL | |
| Modifytime | datetime | NO | | NULL | |
| WHITELIST | varchar (2000) | YES | | NULL | |
| Blacklist | varchar (2000) | YES | | NULL | |
| join_privilege | int (10) | YES | | 0 | |
| exit_privilege | int (10) | YES | | 0 | |
| is_auto | int (1) | NO | | 0 | |
+----------------+---------------+------+-----+---------+-------+

The UIn fields of the two tables are inconsistent, and the UIn of the Groupnam field in the Rm_group_info table must be resolved, and the Rm_user_router field in the LoginName table will be consistent.

1. Back up 2 sheets first

2. Updating synchronization data

Update Rm_user_router INNER JOIN Rm_group_info on Rm_user_router.loginname=rm_group_info.groupname set Rm_group_ Info.uin=rm_user_router.uin;

　　

MySQL Two table has inconsistent field associated fields