user3848417 user3848417 - 7 months ago 90
SQL Question

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

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

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