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."