How to fix " You can't specify target table 'project' for update in FROM clause"

Question

I am getting the error

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

My query

UPDATE project, id_card 
SET project.family_id = id_card.family_new 
WHERE project.PROJECT_ID = id_card.project 
AND  id_card.code IN( select distinct code from id_card ic
inner join  project p on p.PROJECT_ID = ic.project 
AND  p.current_id_card_version = ic.version 
where ic.id_card_status ='ARCHIVED');

Does this answer your question? [You can't specify target table for update in FROM clause](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) — Chris Schaller, Nov 23 '21 at 13:49
Convert WHERE IN to INNER JOIN and move your subquery to the rowsource (UPDATE clause). — Akina, Nov 23 '21 at 13:52

score 1 · Accepted Answer · answered Nov 23 '21 at 13:55

UPDATE project
JOIN id_card ON project.PROJECT_ID = id_card.project
JOIN ( select code 
       from id_card ic
       inner join  project p on p.PROJECT_ID = ic.project 
                            AND p.current_id_card_version = ic.version 
       where ic.id_card_status ='ARCHIVED') subquery ON id_card.code = subquery.code
SET project.family_id = id_card.family_new;

How to fix " You can't specify target table 'project' for update in FROM clause"

1 Answers1