mysql中update后面跟select语句出错

Question

sc 表 有三个字段  sno 学号   cno 课程号  grade 成绩我想让0821102同学的C001课程成绩改变成该门课程的平均成绩，于是我这样写：UPDATE scSET grade = ( SELECT AVG(grade) FROM sc WHERE cno = 'C001' GROUP BY cno) WHERE sno = '0821102'最后提示：[Err] 1093 - You can't specify target table 'sc' for update in FROM clause于是我查到看到网上有些博客说是不能同时select和update同一个表，于是我改成这样：UPDATE scSET grade = ( SELECT sc1.grade from ( SELECT AVG(grade) FROM sc WHERE cno = 'C001' GROUP BY cno )sc1) WHERE sno = '0821102'结果又提示[Err] 1054 - Unknown column 'sc1.grade' in 'field list'这是怎么回事啊，找了很久原因都没找到

Answer 1

你这个sc1.grade 给它命名别名了吗，又没有别名又没有定义MySQL引擎语法上肯定不识别啊

你可以参考下这样写就可以了。MySQL 已经执行成功的，以下代码可以直接复制你查询实现下：

 create table sc(

 sno varchar(20) primary key not null,

 cno varchar(20),

 grade float(6,2) unsigned

 ); 

 insert into sc values('a001','c01',88.5);

 insert into sc values('a002','c01',78.5);

 insert into sc values('a002','c01',78.5);

 insert into sc values('a003','c01',68.5);

 insert into sc values('a004','c01',58.5);

 insert into sc values('a005','c01',18.5);

 insert into sc values('a006','c02',18.5);

 insert into sc values('a007','c02',28.5);

 insert into sc values('a008','c02',38.5);

 insert into sc values('a009','c02',78.5);

 insert into sc values('a010','c03',78.5);

 insert into sc values('a011','c03',48.5);

 insert into sc values('a012','c03',49.5);

 insert into sc values('a013','c04',100);

 insert into sc values('a014','c04',90);

mysql> update sc set grade=(select sc1_avg_grade from((select avg(grade) as sc1_

avg_grade from sc where cno='c01' group by cno) as sc1) ) where cno='c01';

Query OK, 5 rows affected (0.04 sec)

Rows matched: 5  Changed: 5  Warnings: 0

采纳的话别忘记给分哟，亲

 

Answer 2

简单理解为avg(grade) as sc1_avg_grade，为平均成绩avg(grade)命名别名sc1_avg_grade，

同时(select avg(grade) as sc1_avg_grade from sc where cno='c01' group by cno) as sc1 为这个结果集的表也命名个别名sc1，

查询这个别名即可，对这个sc1表进行操作即可，就不会冲突