如何使用另一个 MySQL 表的值来更新一个 MySQL 表中的值?
mysqlmysqli database
要使用另一个 MySQL 表的值来更新一个 MySQL 表中的值，我们需要在 UPDATE 语句的 SET 子句中使用子查询作为表达式。
示例
在此示例中，我们有两个表"student"和"info"。我们将使用"info"表的"remarks"列的值来更新表"student"的"grade"列的值。
mysql> select * from student; +----+---------+-------+ | Id | Name | grade | +----+---------+-------+ | 1 | Rahul | NULL | | 2 | Gaurav | NULL | | 3 | Raman | NULL | | 4 | Harshit | NULL | | 5 | Aarav | NULL | +----+---------+-------+ 5 rows in set (0.01 sec) mysql> select * from info; +------+-----------+ | id | remarks | +------+-----------+ | 1 | Good | | 2 | Good | | 3 | Excellent | | 4 | Average | | 5 | Best | +------+-----------+ 5 rows in set (0.00 sec) mysql> UPDATE STUDENT SET grade = (SELECT remarks from info WHERE info.id = student.id) WHERE id > 0; Query OK, 5 rows affected (0.08 sec) Rows matched: 5 Changed: 5 Warnings: 0
上述查询借助子查询更新了‘student’表的grade列中的值。从以下MySQL查询返回的结果集中可以看出。
mysql> Select * from student; +----+---------+-----------+ | Id | Name | grade | +----+---------+-----------+ | 1 | Rahul | Good | | 2 | Gaurav | Good | | 3 | Raman | Excellent | | 4 | Harshit | Average | | 5 | Aarav | Best | +----+---------+-----------+ 5 rows in set (0.00 sec)
