MySQL You can't specify target table for update in FROM clause

Question

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!

possible duplicate of Mysql error 1093 - Can't specify target table for update in FROM clause — Lukas Eder
– Lukas Eder, Commented May 25, 2014 at 8:26

Patashu · Accepted Answer · 2013-02-10 01:47:13Z

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)

Collectives™ on Stack Overflow

MySQL You can't specify target table for update in FROM clause

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related