--
-- ### Update operation checks
--

create table updcheck1 (  primary a int not null, b string(5) );
create table updcheck2 (  primary a int not null, b string(5) );
create table updcheck3 ( a int, b string(30));

insert into updcheck1 values ( 1, 'close' );
insert into updcheck1 values ( 2, 'close' );
insert into updcheck1 values ( 3, 'open' );

insert into updcheck2 values ( 1, 'close' );
insert into updcheck2 values ( 2, 'xxx' );
insert into updcheck2 values ( 3, 'xxx' );

insert into updcheck3 values ( 1 , 'Alpha');
insert into updcheck3 values ( 1 , 'Beta');
insert into updcheck3 values ( 1 , 'Gamma');
insert into updcheck3 values ( 1 , 'Delta');

select * from updcheck1;

update updcheck1 tx set b = 'XXX' where  b = ( select b from updcheck2 ty where tx.a = ty.a );

select * from updcheck1;

-- simple update on updcheck1
update updcheck1 set b = 'closed' where a = 2 ;

select * from updcheck1;

-- copy b from updcheck to updcheck2
update updcheck2 set b = ( select b from updcheck1 where a = updcheck2.a );

select * from updcheck2;


@
create procedure loopUpdate ( id in string(30), numUpd in int ) 
begin 

   var actUpd int;

   :actUpd = 0;

   while :actUpd < :numUpd
   begin
      update updcheck3 set  a = a + 1 where b = :id;
      :actUpd = :actUpd + 1;
   end;

end;
@

select * from updcheck3;
call loopUpdate('Alpha', 100);
call loopUpdate('Beta', 100 );
call loopUpdate('Gamma', 100 );
call loopUpdate('Delta', 100 );

select * from updcheck3;

drop table updcheck1;
drop table updcheck2;
drop table updcheck3;
drop procedure loopUpdate;


