Table is specified twice, both as a target for 'UPDATE' and as a separate source for data

Question

I am trying to update multiple rows in a column but when I execute query getting error. Somebody guide me where I am wrong. Thanks in advance

  UPDATE TestTaker
  SET TestTaker.Email = 'Replaced'
  WHERE TestTaker.Id IN (SELECT TestTaker.Id FROM TestTaker
  INNER JOIN Questionnaire ON (TestTaker.id = Questionnaire.TestTaker_id)
  WHERE Questionnaire.Project_id IN(SELECT Project.Id FROM Project WHERE NAME LIKE 'abcd'));

@MarkB 'Table is specified twice, both as a target for 'UPDATE' and as a separate source for data' here is the error. — MaxMini, May 06 '16 at 11:29
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) — Matt Raines, May 06 '16 at 11:35
You are using table 'TestTaker' multiple times in that query, so you have to assign a 'alias' for that table and have to specify this alias for every columns wherever you using. — Abdul Rasheed, May 06 '16 at 11:36

score 3 · Accepted Answer · answered May 06 '16 at 11:39

You are using table 'TestTaker' multiple times in that query, so you have to assign a 'alias' for that table and have to specify this alias for every columns wherever you using. Or try to simplify your query like the below one.

UPDATE TestTaker
SET TestTaker.Email = 'Replaced'
WHERE TestTaker.Id IN ( SELECT  Questionnaire.TestTaker_id
                        FROM    Questionnaire
                        WHERE   (TestTaker.id = Questionnaire.TestTaker_id)
                            AND Questionnaire.Project_id IN(    SELECT Project.Id 
                                                                FROM Project WHERE NAME LIKE 'abcd')
                        );

Thank you so much. you solved my problem.It worked :) – MaxMini May 06 '16 at 11:43 — MaxMini, May 06 '16 at 11:43

Table is specified twice, both as a target for 'UPDATE' and as a separate source for data

1 Answers1

Linked

Related