FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

create sequence s_no_seq;

create table car1
(
    S_No Number default s_no_seq.nextval,
    brand varchar2(100),
    Type varchar2(100),
    Cost number,
    Tax number,
    Discount number,
    Launch_Month varchar2(100)
);

create table car2
(
    S_No Number default s_no_seq.nextval,
    brand varchar2(100),
    Type varchar2(100),
    Cost number,
    Tax number,
    Discount number,
    Launch_Month varchar2(100)
);

Insert into CAR1 (BRAND, TYPE, COST, TAX, DISCOUNT, LAUNCH_MONTH) 
values ('Maruti', 'Baleno', 1000000, 5, 5, 'May');
Insert into CAR1 (BRAND,TYPE,COST,TAX,DISCOUNT,LAUNCH_MONTH) 
values ('Maruti', 'Baleno', 2000000, 6, 10, 'May');
Insert into CAR1 (BRAND, TYPE, COST, TAX, DISCOUNT, LAUNCH_MONTH)  
values ('Maruti', 'Baleno', 3000000, 7, 11, 'May');

Insert into CAR2 (BRAND, TYPE, COST, TAX, DISCOUNT, LAUNCH_MONTH) 
values ('Maruti', 'Baleno', 2000000, 10, 6, 'June');
Insert into CAR2 (BRAND, TYPE, COST, TAX, DISCOUNT, LAUNCH_MONTH)  
values ('Maruti', 'Baleno', 2500000, 11, 8, 'June');

merge into car2
using
(
  with
    -- Choose which month to give to each brand and type. If we've got different months, arbitrarily choose one.
    months as (select brand, type, max(launch_month) launch_month from car2 group by brand, type),
    targets as
    (
      select
        s_no, brand, type,
        -- Get its position across all rows with same brand and type: we'll arbitrarily update from the row with the same position in car1.
        row_number() over (partition by brand, type order by s_no) pos
      from car2
      where (brand, type) in (select distinct brand, type from car1)
    ),
    sources as
    (
      select
        car1.*,
        row_number() over (partition by brand, type order by s_no) pos
      from car1
    ),
    matches as
    (
      select
        t.s_no,
        s.brand, s.type,
        s.cost, s.tax, s.discount,
        coalesce(m.launch_month, s.launch_month) launch_month
      from sources s
      left join targets t on t.brand = s.brand and t.type = s.type and t.pos = s.pos
      left join months m on m.brand = s.brand and m.type = s.type
    )
  select * from matches
)
m
on (m.brand = car2.brand and m.type = car2.type and m.s_no = car2.s_no)
when matched then update set cost = m.cost, tax = m.tax, discount = m.discount
when not matched then insert (brand, type, cost, tax, discount, launch_month)
                      values (m.brand, m.type, m.cost, m.tax, m.discount, m.launch_month)
;

select * from CAR2;

S_NO	BRAND	TYPE	COST	TAX	DISCOUNT	LAUNCH_MONTH
4	Maruti	Baleno	1000000	5	5	June
5	Maruti	Baleno	2000000	6	10	June
8	Maruti	Baleno	3000000	7	11	June