Update Mysql With Join and Select

Question

I need to update TblRemHp.tmp with Result of This

Select TblSub1.id from TblCategorias 
left join TblSubcategorias 
on TblSubcategorias.id_categoria = TblCategorias.id
left join TblSub1 
on TblSub1.id_subcategoria = TblSubcategorias.id
left join TblRemHp 
on TblRemHp.id_categoria = TblCategorias.id and TblRemHp.id_subcategoria = TblSubcategorias.id
where TblRemHp.id_categoria = TblCategorias.id 
and TblRemHp.id_subcategoria = TblSubcategorias.id 
and TblRemHp.id_filtro1 = TblSub1.c1

I don't know how to make this.

Can somebody help me please?
Really thanks.

Answer 1

There are two ways to do this:

Using UPDATE and JOIN:

update A 
inner join B ON A.id = B.id
set A.data = 'Hello'
where B.data = 'world'

But since you mentioned that you want to use the result of the query to make an update, a more straightforward method would be to use a CTE.

with select_query AS (
    select 1 as id -- Your select query comes here
) update A set data = 'Hello' where 1 in (select id from select_query);

The select_query CTE creates a "temporary table" for you to query when you UPDATE. You can even just use a subquery, but I tend to prefer using a CTE for its readability.