mysql 根据查询的数据关联更新表中数据
有一个用户表 user
结构图下:
id name
+-----+----------+
| id | name |
+-----+----------+
| 1 | 姓名1 |
| 1 | 姓名2 |
| 1 | 姓名3 |
+-----+----------+
还要一个分数表 user_grade
id user_id user_name grade
其中user_id 关联其user表信息 某天user_grade 表数据不正常了 user_id字段为空 于是乎我需要根据这个表的关联设计关系,去更新user_grade表数据 如
select * from user_grade
+-----+---------+---------+--------------+
| id | user_id | user_name| grade |
+-----+---------+---------+--------------+
| 1 | 0 | 姓名1 | 1 |
| 1 | 0 | 姓名2 | 50 |
| 1 | 0 | 姓名3 | 89 |
+-----+---------+----------+--------------+
执行下面语句 即更新
UPDATE user_grade a LEFT JOIN user b ON b.user_name = a.name SET a.user_id = b.id WHERE b.user_id = 0;
UPDATE user_grade a LEFT JOIN user b ON b.user_name = a.name SET a.user_id = b.id WHERE b.user_id = 0;
结果如下:
+-----+---------+---------+--------------+
| id | user_id | user_name| grade |
+-----+---------+---------+--------------+
| 1 | 1 | 姓名1 | 1 |
| 1 | 2 | 姓名2 | 50 |
| 1 | 3 | 姓名3 | 89 |
+-----+---------+----------+--------------+