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'));

Answer 1

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')
                        );