How to update several fields of a table based on a subquery in MySQL?

Question

How to update several fields of a table based on a subquery in MySQL?

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

What I basically want to do is, update precio_preparación of my table platillo , based on the total thrown by my subquery, which is stored in a temporary table. Practically what my temporary table shows is the following:

What I want to do is that all the dishes of my table platillo that have an id similar to my temporary table, will update the precio_venta based on the total of the temporary table.

This way I have created my temporary table:

create temporary table tablaTemporal as (select id_platillo as id_platillo, round(sum(total),2) as total from platillo_insumo where id_platillo in 
                                                                      (select p.id_platillo from platillo p 
                                                                      inner join platillo_insumo pi on p.id_platillo = pi.id_platillo
                                                                      inner join insumo i on pi.id_insumo = i.id_insumo 
                                                                      where i.id_insumo = 1/*_id_insumo*/) group by id_platillo);

And the query update is like this:

update platillo set precio_preparacion = (select id_platillo from tablaTemporal) where id_platillo in (select id_platillo from tablaTemporal);

But at the moment of running the query of update , I get the following error:

Error Code: 1137. Can't reopen table: 'tablaTemporal'

The truth is that I am a relatively new person in this wanting to do something out of reach, I hope you can help me, Thanks.

PS: "This was the only way I could think of to do it, if you have a better idea, please let me know."

mysql mysql-workbench

asked by U.C 06.03.2018 в 00:37

source

2 answers

Display data in ListView in reverse Problem redirecting the / slash at the end (/) .htaccess

score 1 · Answer 1

Your update string is incorrect ... let's analyze your SQL

update platillo 
set precio_preparacion = (select id_platillo from tablaTemporal) 
where id_platillo in (select id_platillo from tablaTemporal)

Your select tells the database, try to update the table saucer, putting as price, everything that returns that select (which is all your temporary table). And then you give it a search condition, which says it has to be, in the whole temporary table.

It seems that what you really wanted was something like this:

update platillo as p
set p.precio_preparacion = (select total from tablaTemporal as t where t.id_platillo = p.id_platillo(*)) 
where p.id_platillo in (select id_platillo from tablaTemporal)

(*) As I do not know the name of your field in the price_preparation table, I'm assuming it's the same. Replace it with the corresponding one.

score 0 · Answer 2

In the end this was the final code that solved my problem:

update platillo pl set pl.precio_preparacion = (select t.total from (select id_platillo as id_platillo, round(sum(total),2) as total from platillo_insumo where id_platillo in 
                                                                      (select p.id_platillo from platillo p 
                                                                      inner join platillo_insumo pi on p.id_platillo = pi.id_platillo
                                                                      inner join insumo i on pi.id_insumo = i.id_insumo 
                                                                      where i.id_insumo = 1)group by id_platillo) as t where t.id_platillo = pl.id_platillo)
where pl.id_platillo in (select t.id_platillo from (select id_platillo as id_platillo from platillo_insumo where id_platillo in 
                                                                      (select p.id_platillo from platillo p 
                                                                      inner join platillo_insumo pi on p.id_platillo = pi.id_platillo
                                                                      inner join insumo i on pi.id_insumo = i.id_insumo 
                                                                      where i.id_insumo = 1))as t);

When executing the code that was previously provided to me:

update platillo as p
set p.precio_preparacion = (select total from tablaTemporal as t where t.id_platillo = p.id_platillo(*)) 
where p.id_platillo in (select id_platillo from tablaTemporal)

I was throwing the following error:

Error Code: 1137. Can't reopen table: 't'

I think the temporary table was causing me a problem, so I chose to do it this way, based on the code they gave me. Thanks for your help.