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

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

CodePudding user response：

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;