clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601549 fiddles created (47965 in the last week).

select * from V$VERSION;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production 0
 hidden batch(es)


alter session set nls_date_format='yyyy-mm-dd'
 hidden batch(es)


create table data (id, company_name, vat_id, url, created) as select 1, 'aaa', '123', 'aaa.com', date'2021-05-01' from dual union all select 2, 'bbb', '111', 'bbb.de' , date'2021-05-01' from dual union all select 3, 'ccc', '345', 'ccc.de ', date'2021-05-01' from dual union all select 5, 'ddd', '098', 'ddd.de' , date'2021-05-21' from dual
4 rows affected
 hidden batch(es)


create table data_hist (tag, id, company_name, vat_id, url, delete_flag) as select date'2021-05-25', 1, 'aaa', '123', 'aaa.com', null from dual union all select date'2021-05-25', 3, 'ccc', '345', 'ccc.de ', null from dual union all select date'2021-05-25', 4, 'xxx', '789', 'xxx.com', 1 from dual union all select date'2021-05-21', 1, 'aaa-2', '123', 'aaa.com', null from dual union all select date'2021-05-21', 3, 'ccc', '345-2', 'ccc.de ', null from dual union all select date'2021-05-20', 1, 'aaa-3', '123-3', 'aaa.com', null from dual union all select date'2021-05-20', 2, 'bbb', '111', 'bbb.de ', null from dual
7 rows affected
 hidden batch(es)


select * from data;
ID COMPANY_NAME VAT_ID URL CREATED
1 aaa 123 aaa.com 2021-05-01
2 bbb 111 bbb.de 2021-05-01
3 ccc 345 ccc.de 2021-05-01
5 ddd 098 ddd.de 2021-05-21
 hidden batch(es)


select * from data_hist;
TAG ID COMPANY_NAME VAT_ID URL DELETE_FLAG
2021-05-25 1 aaa 123 aaa.com
2021-05-25 3 ccc 345 ccc.de
2021-05-25 4 xxx 789 xxx.com 1
2021-05-21 1 aaa-2 123 aaa.com
2021-05-21 3 ccc 345-2 ccc.de
2021-05-20 1 aaa-3 123-3 aaa.com
2021-05-20 2 bbb 111 bbb.de
 hidden batch(es)


with param (asofday) as ( select date'2021-05-20' from dual ), t (tag, id, company_name, vat_id, url, delete_flag) as ( select date'9999-12-31', id, company_name, vat_id, url, null from data, param where created <= param.asofday union all select tag, id, company_name, vat_id, url, delete_flag from data_hist) select lastchanged, id, company_name, vat_id, url, delete_flag from t t1 cross apply ( select max (t2.tag) keep (dense_rank last order by tag desc) lastchanged from t t2, param where t2.id = t1.id and t2.tag >= param.asofday group by t2.id ) where tag = lastchanged order by id
LASTCHANGED ID COMPANY_NAME VAT_ID URL DELETE_FLAG
2021-05-20 1 aaa-3 123-3 aaa.com
2021-05-20 2 bbb 111 bbb.de
2021-05-21 3 ccc 345-2 ccc.de
2021-05-25 4 xxx 789 xxx.com 1
 hidden batch(es)


create global temporary table stage (id, company_name, vat_id, url, created) as select * from data where 1=0
 hidden batch(es)


create or replace type stagerow is object ( id int, company_name varchar(96), vat_id varchar(96), url varchar(96), created date, flagexists int) /
 hidden batch(es)


create or replace type stagetab is table of stagerow /
 hidden batch(es)


insert into stage select 1, 'aaa', '123', 'aaa.com', date'2021-05-01' from dual union all select 2, 'bbb', '111', 'bbb.de' , date'2021-05-01' from dual union all select 3, 'ccc', '345', 'ccc.de ', date'2021-05-01' from dual union all select 4, 'xxx', '789', 'xxx.com', date'2021-05-01' from dual union all select 5, 'ddd', '098', 'ddd.de' , date'2021-05-21' from dual
5 rows affected
 hidden batch(es)


select * from stage
ID COMPANY_NAME VAT_ID URL CREATED
1 aaa 123 aaa.com 2021-05-01
2 bbb 111 bbb.de 2021-05-01
3 ccc 345 ccc.de 2021-05-01
4 xxx 789 xxx.com 2021-05-01
5 ddd 098 ddd.de 2021-05-21
 hidden batch(es)


declare st stagetab; begin select stagerow ( s.id, s.company_name, s.vat_id, s.url, s.created, case when d.id is null then 0 else 1 end) bulk collect into st from stage s left join data d on d.id = s.id; dbms_output.put_line ('staged '||sql%rowcount); insert into data_hist select trunc (sysdate), id, company_name, vat_id, url, null from data d where exists ( select 1 from table (st) s where s.id = d.id and s.flagexists = 1); dbms_output.put_line ('copied '||sql%rowcount); delete from data d where exists ( select 1 from table (st) s where s.id = d.id and s.flagexists = 1); dbms_output.put_line ('deleted '||sql%rowcount); insert into data select id, company_name, vat_id, url, created from table (st); dbms_output.put_line ('inserted '||sql%rowcount); end; /
1 rows affected dbms_output: staged 5 copied 4 deleted 4 inserted 5
 hidden batch(es)