Update and Select in the same sentence

Question

Update and Select in the same sentence

Navigation

#1 by (1 votes)

0

I have a sql statement where I want to join update with select but it gives me the following error:

Error Code: 1093. Table 'a' is specified twice, both as a target for 'UPDATE' and as a separate source for data 0.000 sec

My sentence is as follows:

update inv_articulo set ref = (select concat(c.prefijo_cat, '-', lpad(a.pk_articulo, 4, '0')) from inv_categoria as c 
inner join inv_tipoarticulo as t on c.pk_categoria = t.fk_categoria
inner join inv_articulo as a on t.pk_tipo = a.tipo_articulo 
where a.tipo_articulo = 26 and a.pk_articulo = 16)

Any help?

mysql sql

asked by Xerox 11.10.2018 в 16:07

source

1 answer

How to select only 1 radio button from 3 different lists, jquery? How do I modify the titles of a jTable in Java runtime?

score 1 · Accepted Answer

In mysql , first place all the join and then the SET sentence. It should be as follows:

update from inv_categoria as c 
inner join inv_tipoarticulo as t on c.pk_categoria = t.fk_categoria
inner join inv_articulo as a on t.pk_tipo = a.tipo_articulo 
set ref = (select concat(c.prefijo_cat, '-', lpad(a.pk_articulo, 4, '0')) 
where a.tipo_articulo = 26 and a.pk_articulo = 16)