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

0
votes

I want to make an update on a table with the results of a query and that records exist in the tables, my SLQ is:

UPDATE reparticion SET responsable_nombre_completo = (select CONCAT_WS(',',persona.nombre,persona.apellido) FROM persona INNER JOIN usuario on usuario.cuil = persona.cuil) WHERE reparticion.id IN (select persona.reparticion_id FROM persona INNER JOIN usuario on usuario.cuil = persona.cuil INNER JOIN reparticion on reparticion.id = persona.reparticion_id);

but I get the following error:

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

1
mysql

1 Answers

1
votes

The better approach would be to use join update instead of sub-queries. 

update reparticion r
join persona p on p.reparticion_id = r.id
join usuario u on u.cuil = p.cuil
set r.responsable_nombre_completo = CONCAT_WS(',',p.nombre,p.apellido)