Sub-query in "MYSQL" update/delete/select statement

When an UPDATE or DELETE statement contains a subquery, the table in the subquery cannot be in the update or DELETE statement, as if it contains runtime error, but when the SELECT statement contains a subquery, the table in the subquery can be in the SELECT statement.

For example, change the name of a student with a total of less than 100 to a genius

Select stu_id from Score GROUP by stu_id have sum (grade) <100; #查询总成绩小于100的学生ID
Update students set Name= ' genius ' where ID in (select stu_id from Score GROUP by stu_id have sum (grade) <100);

In addition, when you do the query, you can use a single table do not use multiple tables. In this way, the logic is clear, the probability of error is much less.

Sub-query in "MYSQL" update/delete/select statement