CODEWITHSUNDEEP
mysql
Vladimir Castañeda
Vladimir Castañeda

Reputation: 7

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

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

Upvotes: 0

Views: 61

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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)

Upvotes: 1

Related Questions