Update single-row table in pl/pgsql-while loop-CodePudding

create table data(a1 float, a2 float);
insert into data values(1::float, 1::float);

do $$
declare 
   counter integer := 0;
begin
   while counter < 50 loop
      --SAVE THIS INTO DATA      
      SELECT a1-0.1*random(),
              a2-0.1*random()
      from data group by a1, a;
      --SAVE THIS INTO DATA (END)
      counter := counter   1;
   end loop;
end$$;

I would like to save the values of a1 and a2 into the first tuple of data. (Basically a running tally)

How can I save the result of a SELECT into a existing table?

CodePudding user response：

This would be easier with a PRIMARY KEY on the table:

create table data(id int primary key, a1 float, a2 float);
insert into data values(1, 1, 1);
select * from data;
 id | a1 | a2 
---- ---- ----
  1 |  1 |  1

update data set a1 = a1 - 0.1*random(), a2 = a2 - 0.1*random() where id =1;
select * from data;
 id |         a1         |        a2        
---- -------------------- ------------------
  1 | 0.9039355377682678 | 0.98859843416964

Then incorporate the UPDATE into the counter loop:

do $$              
declare 
   counter integer := 0;
begin
   while counter < 50 loop
      --SAVE THIS INTO DATA      
     update data set a1 = a1 - 0.1*random(), a2 = a2 - 0.1*random() where id =1;  
    --SAVE THIS INTO DATA (END)
      counter := counter   1;
   end loop;
end$$;

select * from data;
 id |         a1          |         a2         
---- --------------------- --------------------
  1 | -1.3146675853588001 | -1.216530003992667