Home » SQL & PL/SQL » SQL & PL/SQL » Merge using rownum (12.1.0.1 )
Merge using rownum [message #687949] |
Mon, 31 July 2023 03:25 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have the following case:
create table test_main
(
id number primary key,
ag_num number,
mdate date ,
value number
);
create table test_log
(
fk_ag_num number,
ldate date,
value number,
fk_id number
);
insert all
insert all
into test_main(id, ag_num, mdate, value) values (10, 50, trunc(sysdate), -15.5)
into test_main(id, ag_num, mdate, value) values (22, 50, trunc(sysdate), -15.5)
into test_main(id, ag_num, mdate, value) values (13, 60, trunc(sysdate), -115.5)
into test_main(id, ag_num, mdate, value) values (400, 60, trunc(sysdate), -115.5)
into test_main(id, ag_num, mdate, value) values (51, 90, trunc(sysdate), -215.5)
into test_main(id, ag_num, mdate, value) values (16, 90, trunc(sysdate), -215.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
select * from dual;
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (50, trunc(sysdate), -15.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (60, trunc(sysdate), -115.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
into TEST_LOG(FK_AG_NUM, ldate, VALUE) values (90, trunc(sysdate), -215.5)
select * from dual;
What I need to do is to update fk_id in test_log table from test_main. Due to duplication, I need the first item to be linked with its first matching peer such as:
select rownum ord, due.* from test_main due where mdate = trunc(sysdate) and value < 0 order by due.ag_num;
select rownum ord, trn.* from TEST_LOG trn where trn.ldate = trunc(sysdate) and value < 0 order by trn.FK_AG_NUM;
MERGE INTO (select rownum ord, trn.* from TEST_LOG trn where trn.ldate = trunc(sysdate) and value < 0 order by trn.FK_AG_NUM) e
USING (
select rownum ord, due.* from test_main due where mdate = trunc(sysdate) and value < 0 order by due.ag_num
) h
ON (e.FK_AG_NUM = h.ag_num and e.ldate = h.mdate and e.value = h.value and e.ord = h.ord)
WHEN MATCHED THEN
UPDATE SET e.fk_id = h.id
;
Unfortunately my idea did not work as I get: 1 ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
I also tried normal update but I failed as I still had to add the additional row to match each record from the log table to its peer in order in the main table.
Please provide your support
Thanks,
Ferro
|
|
|
Re: Merge using rownum [message #687950 is a reply to message #687949] |
Mon, 31 July 2023 08:05 |
|
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
It seems you are assuming that the rows in TEST_LOG will have corresponding matches in TEST_MAIN (same number of rows for each AG_NUM in the two tables, with the same values, etc.) - I will make the same assumption below.
In the join conditions you shouldn't have to test for LDATE = MDATE - you already filtered for both to equal TRUNC(SYSDATE), so they must be equal already.
Other than that, you need to perform the join in the SRC view (the USING clause), and use ROWID in TEST_LOG in the TGT view (the INTO clause) to match rows in the table to rows in the result of the join. Like this:
merge
into (
select rowid as rid, fk_id
from test_log
where ldate = trunc(sysdate) and value < 0
) tgt
using (
with
m (id, ag_num, mdate, value, ord) as (
select tm.*, rownum
from test_main tm
where mdate = trunc(sysdate) and value < 0
order by ag_num
)
, l (rid, fk_ag_num, ldate, value, fk_id, ord) as (
select tl.rowid, tl.*, rownum
from test_log tl
where ldate = trunc(sysdate) and value < 0
order by fk_ag_num
)
select l.rid as rid, m.id as id
from m join l on l.fk_ag_num = m.ag_num and l.value = m.value and l.ord = m.ord
) src
on (tgt.rid = src.rid)
when matched then update set tgt.fk_id = src.id
;
|
|
|
Re: Merge using rownum [message #687959 is a reply to message #687950] |
Tue, 01 August 2023 05:18 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Dear mathguy,
Thanks a lot for your help.
Quote:
In the join conditions you shouldn't have to test for LDATE = MDATE - you already filtered for both to equal TRUNC(SYSDATE), so they must be equal already.
Right you are, it was extra from previous trials.
Quote:
Other than that, you need to perform the join in the SRC view (the USING clause), and use ROWID in TEST_LOG in the TGT view (the INTO clause) to match rows in the table to rows in the result of the join.
Thanks a lot.
Ferro
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:49:48 CDT 2024
|