Why I get Unknown column problem with CTE in MySQL

Question

I am getting the next error with a select query. If I get rid off the update clause the error disappears:

Error Code: 1054. Unknown column 'CTE.id' in 'where clause'

// SQL

with CTE  as (
       select T1.id,T1.pvalor_recebido
        FROM (
        select  A.id,sum(B.valor_recebido) as pvalor_recebido
            from cl_tiss_sadt A
            join cl_tiss_sadt_proced  B on B.id_tiss_sadt=A.id
            where  A.isguiapadrao is null and A.deleted<>1 
            and A.cab_nrguiaprest='1020001442' and A.lote_id=176
            group by A.id
        union  all
            select  A.id,sum(C.valor_recebido) as pvalor_recebido
            from cl_tiss_sadt A
            join cl_tiss_sadt_odesp C on C.id_tiss_sadt=A.id
            where  A.isguiapadrao is null and A.deleted<>1
            and A.cab_nrguiaprest='1020001442' and A.lote_id=176
            group by A.id ) T1 )
update cl_tiss_sadt BB set BB.valor_recebido=CTE.pvalor_recebido where BB.id=CTE.id

if I use the next, I get no error and the result is:

id       pvalor_recebido
4200     null

//query without error

with CTE  as (select T1.id,T1.pvalor_recebido
        FROM (
        select  A.id,sum(B.valor_recebido) as pvalor_recebido
            from cl_tiss_sadt A
            join cl_tiss_sadt_proced  B on B.id_tiss_sadt=A.id
            where  A.isguiapadrao is null and A.deleted<>1 
            and A.cab_nrguiaprest='1020001442' and A.lote_id=176
            group by A.id
        union  all
            select  A.id,sum(C.valor_recebido) as pvalor_recebido
            from cl_tiss_sadt A
            join cl_tiss_sadt_odesp C on C.id_tiss_sadt=A.id
            where  A.isguiapadrao is null and A.deleted<>1
            and A.cab_nrguiaprest='1020001442' and A.lote_id=176
            group by A.id) T1
)
select * from CTE where CTE.id>0

Answer 1

Your update syntax is incorrect:

with CTE as (
    select id, pvalor_recebido
    <cte sql not relevant>
)
update cl_tiss_sadt, cte
set cl_tiss_sadt.valor_recebido = CTE.pvalor_recebido
where cl_tiss_sadt.id = CTE.id

See live demo.