Forum Stats

  • 3,875,419 Users
  • 2,266,914 Discussions
  • 7,912,203 Comments

Discussions

Stored procedure updating same value for al the records

Albert Chao
Albert Chao Member Posts: 207 Red Ribbon
edited Oct 31, 2022 7:51AM in SQL & PL/SQL
create table test_staging(value varchar2(30));
insert into test_staging values('A');
insert into test_staging values('B');
insert into test_staging values('C');
insert into test_staging values('D');

create sequence test_main_sq;
create table test_main(id number,value varchar2(30));

create or replace procedure p_test
as
begin
v_sql := 'TRUNCATE TABLE test_main';
EXECUTE IMMEDIATE v_sql;

insert into test_main(value)
select value from test_staging;

for i in(select value from test_staging)
loop
update test_main set id = (select max(rownum) from test_staging) - 1;
end loop;
end;
/

Database: Oracle Live SQL (19c)

Problem statement:

I want to insert records from staging table into the main table but id column of main table should be updated in reversed order e.g last record of staging table should have id column value as 1, second last as 2, and so on. So the first record of the main table should have maximum id.

Expected rows of test_main table:

+----+-------+
| id | value |
+----+-------+
|  4 | A     |
|  3 | B     |
|  2 | C     |
|  1 | D     |
+----+-------+

But as per my logic, it is updating 3 for all the Id's.


Also asked on SF:https://stackoverflow.com/questions/74259680/stored-procedure-updating-same-value-for-al-the-records

Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond
    Answer ✓

    As User_3ABCE points out, what is the difference between this requirement and what you asked back in September?

    You say:

    I want to insert records from staging table into the main table but id column of main table should be updated in reversed order e.g last record of staging table should have id column value as 1, second last as 2, and so on. So the first record of the main table should have maximum id.

    What defines the order of the records? Is it the alphabetical ordering of the value? Remember it can't be the order the records are inserted into the table, because oracle could insert them into any 'gaps' in the table, which isn't necessary the same order that you supply the insert statements.

    You appear to have created a sequence, but you've not attempted to use it. Is the sequence relevant to your requirement?

    You are updating all the rows of the table with your update statement that is inside the loop, so it's not even attempting to update just the row with the relevant value from the loop. Why?

    Are you just looking for something like this...

    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure p_test as
      2  begin
      3    execute immediate 'truncate table test_main';
      4    merge into test_main t
      5    using (select row_number() over (order by value desc) as id
      6                 ,value
      7           from   test_staging
      8          ) s
      9    on (t.id = s.id)
     10    when not matched then
     11      insert (id, value) values (s.id, s.value);
     12    commit;
     13* end;
    SQL> /
    
    Procedure created.
    
    SQL> exec p_test;
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test_main order by value;
    
            ID VALUE
    ---------- ------------------------------
             4 A
             3 B
             2 C
             1 D
    

    That certainly meets your required output... but whether it meets the required logic is anybody's guess.

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond
    Answer ✓

    As User_3ABCE points out, what is the difference between this requirement and what you asked back in September?

    You say:

    I want to insert records from staging table into the main table but id column of main table should be updated in reversed order e.g last record of staging table should have id column value as 1, second last as 2, and so on. So the first record of the main table should have maximum id.

    What defines the order of the records? Is it the alphabetical ordering of the value? Remember it can't be the order the records are inserted into the table, because oracle could insert them into any 'gaps' in the table, which isn't necessary the same order that you supply the insert statements.

    You appear to have created a sequence, but you've not attempted to use it. Is the sequence relevant to your requirement?

    You are updating all the rows of the table with your update statement that is inside the loop, so it's not even attempting to update just the row with the relevant value from the loop. Why?

    Are you just looking for something like this...

    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure p_test as
      2  begin
      3    execute immediate 'truncate table test_main';
      4    merge into test_main t
      5    using (select row_number() over (order by value desc) as id
      6                 ,value
      7           from   test_staging
      8          ) s
      9    on (t.id = s.id)
     10    when not matched then
     11      insert (id, value) values (s.id, s.value);
     12    commit;
     13* end;
    SQL> /
    
    Procedure created.
    
    SQL> exec p_test;
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test_main order by value;
    
            ID VALUE
    ---------- ------------------------------
             4 A
             3 B
             2 C
             1 D
    

    That certainly meets your required output... but whether it meets the required logic is anybody's guess.

  • Stax
    Stax Member Posts: 184 Silver Badge

    hi,Albert Chao

    insert into test_main(id,value)
    select rownum,value from test_staging;
    
    select * from test_main
    ID	VALUE
    1	A
    2	B
    3	C
    4	D
    4 rows returned in 0.11 seconds	        	Download
    
    truncate table test_main
    
    insert into test_main(id,value)
    select rownum,value from ( select * from test_staging order by value desc);
    
    select * from test_main
    ID	VALUE
    1	D
    2	C
    3	B
    4	A
    4 rows returned in 0.01 seconds	        	Download
    


    Albert Chao