mysql 如何将女学生中低于总平均成绩的成绩提高5%?

Question

mysql> desc SC ;
Field	Type	Null	Key	Default	Extra
SID	int(11)	NO	PRI	NULL
CID	int(11)	NO	PRI	NULL
GRADE	int(11)	YES		NULL

3 rows in set (0.00 sec)

mysql> desc S ;
Field	Type	Null	Key	Default	Extra
SID	int(11)	NO	PRI	NULL
SNAME	varchar(20)	YES		NULL
AGE	int(11)	YES		NULL
SEX	int(11)	YES		NULL

表结构如上, 我写了一个sql:

update SC set GRADE = (GRADE+GRADE*0.05) where GRADE < (select AVG(GRADE) from SC x) and SID in (select SID from S where SEX = 1) ;

但是报错:

ERROR 1093 (HY000): You can't specify target table 'SC' for update in FROM clause

我到mysql的官网上也没找到解决的办法, 看了几个博客说是要使用中间表, 但是还是写不出来, 那么正确的sql是怎样的呢? 望告知

Answer 1

begin
declare n_avg  number;
select avg(grade) into n_avg from SC;
update SC set GRADE = GRADE*1.05 where grade<n_avg and sid in (select SID from S where SEX=1);
end

Answer 2

select @avgs:=(select avg(grade) from SC);
update SC set GRADE = (GRADE+GRADE*0.05) where GRADE < @avgs and SID in (select SID from S where SEX = 1) ;

Answer 3

The CID in the SC table represents the course ID, so it should be classified by course. For example, the average score of Chinese is 80, then the score of girls whose Chinese score is lower than 80 will be increased by 5%, and then each course will be Follow this logic

Is my understanding correct? If it's right, I'll type the code.