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.

CodePudding user response：

You can use this for insert:

Select 
    ROW_NUMBER() OVER(Order By VALUE Desc) "RN",
    VALUE "VAL"
From test_staging;

There is no need for updating IDs when you can insert them along with values. Your sql within the update loop returns 3 for every record becouse the Select Max(ROWNUM) From test_staging is a fixed value of 4. You can just put it all in one insert command:

Insert Into test_main(ID, VALUE)
Select 
    ROW_NUMBER() OVER(Order By VALUE Desc) "RN",
    VALUE "VAL"
From test_staging;

I used ROW_NUMBER() analytic function. More about it (and the others) at: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
Regards...