CODEWITHSUNDEEP
mysqlsql-updatesubquery
osakagreg
osakagreg

Reputation: 577

Update with SubQuery from same table

Is it possible to update using a subquery from the same table?

I get this error:

1093 - You can't specify target table 'tasks' for update in FROM clause

update tasks SET completed_form = 'y' AND all_forms_in = 'y' where EID in (select EID from tasks WHERE completed_form = 'y' AND all_forms_in = 'n' group by EID having count(*) > 1);

UPDATE

I found that if I wrapped the query in another select it worked. As referenced here: MySQL Error 1093 - Can't specify target table for update in FROM clause

update tasks SET all_forms_in = 'y' where EID in (SELECT * FROM (select EID from tasks WHERE completed_form = 'y' AND all_forms_in = 'n' group by EID having count(*) > 1)AS b);

Upvotes: 0

Views: 1341

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

MySQL supports JOIN in UPDATE statements, so you could do this without subqueries. You'd have to join to two distinct rows with your completed criteria to simulate the count(*) > 1.

UPDATE tasks AS t1
INNER JOIN tasks AS t2
  ON (t1.EID = t2.EID AND t2.completed_form = 'y' AND t2.all_forms_in = 'n')
INNER JOIN tasks AS t3
  ON (t1.EID = t3.EID AND t3.completed_form = 'y' AND t3.all_forms_in = 'n'
  AND t2.primary_key <> t3.primary_key)
SET t1.completed_form = 'y', 
    t1.all_forms_in = 'y';

Upvotes: 1

Related Questions