I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist.
Like this...
do $$
declare dados Record;
begin
for dados in
select
vd."Id"
from "Vendas" vd
inner join "ItensVendas" iv on iv."VendaId" = vd."Id"
where vd."Especie" = 'NFE' and coalesce(vd."ClienteId", 0) = 0 and iv."CodCFOP" in (5949, 5927)
group by vd."ChaveNFE", vd."Id", vd."DataCadastro"
loop
execute format('update "Vendas" set "EhConsumoProprio" = true where "Id" = %L', dados."Id");
end loop;
end $$;
And again...
do $$
declare dados Record;
declare cmd varchar(300);
begin
for dados in
select
vd."ChaveNFE",
vd."Id",
vd."DataCadastro"
from
"Vendas" vd
inner join "ItensVendas" iv on
iv."VendaId" = vd."Id"
where
vd."Especie" = 'NFE'
and coalesce(vd."ClienteId",0)= 0
and iv."CodCFOP" in (5949, 5927)
group by vd."ChaveNFE",
vd."Id",
vd."DataCadastro"
loop
raise notice '%',dados."Id";
update "Vendas" set "EhConsumoProprio"=true where "Id"=dados."Id"; -- It don't persists
cmd := format('update "Vendas" set "EhConsumoProprio"=true where "Id"=%L', dados."Id");
raise notice '%', cmd;
execute cmd; -- It don't persists
end loop;
end $$;
Asked by Jones Romão
(11 rep)
Mar 15, 2019, 01:10 PM
Last activity: May 29, 2025, 04:04 AM
Last activity: May 29, 2025, 04:04 AM