0

When I run this query

update Apply 
set major='CSE'
where major='EE' and sID in (select sID from Student
                             where GPA >= all (select GPA from Student
                       where sID in (select sID from Apply
                             where major='EE')));

it returns an error: ERROR 1093 (HY000): You can't specify target table 'Apply' for update in FROM clause. But it succeeds in another MySQL.

if I add "as tmp" to the end of the code, it returns an error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as tmp' at line 6

Can somebody tell me the reason and how can I modify the code? Thank you very much!

randomp
  • 357
  • 1
  • 5
  • 18
  • possible duplicate of [Mysql error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) – Lukas Eder May 25 '14 at 08:26

1 Answers1

1

Problem: You're referencing the table Apply twice in two different contexts.

Solution: Give them different aliases, e.g. Apply as a1 and Apply as a2

(Maybe this also needs to be done for Student)

Patashu
  • 21,443
  • 3
  • 45
  • 53