0

I need to update product_details table with unique ID(CHARGE_NUMBER) againt each batch_ID and for the next batch ID unique ID again will start with 1

I have write a code using sequence. 

update product_details 
set charge_number = charge_num_seq.nextval
process_id = i.batch_id ;

But there is one question "What if multiple concurrent transaction comes ? It may not give correct result"

CREATE OR REPLACE PROCEDURE cbf_update_adjustment_seq(p_profile_id  character varying) AS
begin
 for j in (select * from inv_gen_trans where profile_id = p_profile_id) loop
    begin


             update product_details 
            set adj_seq = update_adj_seq.nextval  ,
                 charge_number = charge_num_seq.nextval
            where
             process_id = j.batch_id ;

            ALTER SEQUENCE charge_num_seq  RESTART WITH 1;
            ALTER SEQUENCE update_adj_seq  RESTART WITH 1;


        ALTER SEQUENCE update_adj_seq  RESTART WITH 1;

    exception
    when others then 
    dbms_output.put_line('Error:'||errmsg);

    end;
  end loop;


end

One profile having some batch ID 3 - 4 batch ID's in inv_gen_trans table and below are the expected output in product_details table

process_id     |charge_number 
---------------|--------------
20190430810985 |1             
20190430810985 |2             
20190430810986 |1             
20190430810986 |2             
20190430810987 |1
Deepak Patil
  • 17
  • 8
  • Sir, Its Postgress. Could you please help me to create update statement. – Deepak Patil May 08 '19 at 18:34
  • I can able to execute this procedure my system. I m using PgAdmin III tool. – Deepak Patil May 08 '19 at 18:42
  • 'EnterpriseDB 10.1.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit' – Deepak Patil May 08 '19 at 18:45
  • What is the primary key of that table? –  May 08 '19 at 18:47
  • There No Primary Key in product_details table. – Deepak Patil May 08 '19 at 18:50
  • 1
    How do you expect to update a single row if you don't have a primary or unique key? –  May 08 '19 at 19:14
  • Thanx for your kind and quick support. Below query is working for me.... update product_details f set charge_number = s.charge_number from ( select ctid, row_number() over (order by ctid) as charge_number from product_details where process_id=20190430810985 ) s where f.ctid = s.ctid and F.process_id=20190430810985 – Deepak Patil May 09 '19 at 04:16

0 Answers0