这两个查询本身没有什么实际意义,仅是作为示意。
这个查询可以顺利运行:SELECT salary
FROM professor
WHERE Id IN
(
SELECT Id
FROM professor
)而这个却不行:UPDATE professor
SET Salary=1.1*Salary
WHERE Id IN
(
SELECT Id
FROM professor
)请问后者该如何改呢?
这个查询可以顺利运行:SELECT salary
FROM professor
WHERE Id IN
(
SELECT Id
FROM professor
)而这个却不行:UPDATE professor
SET Salary=1.1*Salary
WHERE Id IN
(
SELECT Id
FROM professor
)请问后者该如何改呢?
UPDATE professor
SET Salary=(select 1.1*Salary FROM professor
WHERE Id IN
(
SELECT Id
FROM professor
));
UPDATE professor
SET Salary=1.1*Salary
WHERE Id IN
(
SELECT Id
FROM professor
)这个语句本身就有问题,你既然要把professor表中的所有Salary字段都修改,那where条件就一点意义都没有。你到底想做什么那?
后者提示的错误信息是“You can't specify target table 'professor' for update in FROM clause”
请问原因何在?
SET Salary=1.1*Salary
WHERE Id IN
(
SELECT DISTINCT p.Id
FROM professor p,teaching t
WHERE (t.Semester='S1997' OR t.Semester='F1997') AND p.Id=t.ProfId AND p.Age<40 AND t.CrsCode='MAT123'
);提示错误:
You can't specify target table 'professor' for update in FROM clause
似乎在UPDATE之后的子查询中的FROM子句中不能出现被UPDATE的表,这是为什么呢?
请指教。
SET Salary=1.1*(select Salary FROM professor
WHERE Id IN
(
SELECT Id
FROM professor
));
UPDATE professor inner join teaching t
on professor.id=t.ProfId
SET professor.Salary=1.1*professor.Salary
WHERE (t.Semester='S1997' OR t.Semester='F1997') AND professor.Age<40 AND t.CrsCode='MAT123'
;
UPDATE professor as a, professor as b SET a.Salary=1.1*a.Salary WHERE a.Id = b.Id;
FROM professor
WHERE Id IN
(select * from(
SELECT Id
FROM professor) temptb);但是效率低,资源开销较大,不建议使用。