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表进行操作即可，就不会冲突