Oracle developer intermediate Lesson 8 (Merge) experiment

summary

This experiment refers to the in DevGym Experimental guide.

Create environment

create table bricks_for_sale (
  colour   varchar2(10),
  shape    varchar2(10),
  price    number(10, 2),
  primary key ( colour, shape )
);

create table purchased_bricks (
  colour   varchar2(10),
  shape    varchar2(10),
  price    number(10, 2),
  primary key ( colour, shape )
);

insert into bricks_for_sale values ( 'red', 'cube', 4.95 );
insert into bricks_for_sale values ( 'blue', 'cube', 7.75 );
insert into bricks_for_sale values ( 'blue', 'pyramid', 9.99 );

commit;

The data are as follows:

SQL> select * from bricks_for_sale;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube           4.95
blue      cube           7.75
blue      pyramid        9.99

SQL> select * from purchased_bricks;

no rows selected

Insert then update or update then insert

When adding rows to a table, you sometimes need to execute the logic of "insert if it does not exist and update if it exists", instead of writing separate insert and update statements. This is upsert.

For example, the following PL/SQL implements upsert by updating first and then inserting. The key point is to judge whether the update is successful by whether sql%rowcount is 0. The premise here is that the table has a corresponding primary key, otherwise update may modify multiple rows:

declare
  l_colour varchar2(10) := 'blue';
  l_shape  varchar2(10) := 'pyramid';
  l_price  number(10, 2) := 9.99;
begin

  update purchased_bricks pb
  set    pb.price = l_price
  where  pb.colour = l_colour
  and    pb.shape = l_shape;

  if sql%rowcount = 0 then
  
    insert into purchased_bricks
    values ( l_colour, l_shape, l_price );
  
  end if;
  
end;
/

select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid        9.99

Another way to implement upsert is to insert first and then update. The key point is to judge whether insert will cause duplicate indexes:

declare
  l_colour varchar2(10) := 'blue';
  l_shape  varchar2(10) := 'pyramid';
  l_price  number(10, 2) := 15.49;
begin

  insert into purchased_bricks
  values ( l_colour, l_shape, l_price );
  
exception
  when DUP_VAL_ON_INDEX then
  
    update purchased_bricks pb
    set    pb.price = l_price
    where  pb.colour = l_colour
    and    pb.shape = l_shape;
    
end;
/

select * from purchased_bricks;

commit;

Depending on the data, both methods may have redundant operations. The better method is to use Merge.

Merging New Values

Merge is a statement that allows you to execute inserts or updates as needed. To use it, you need to specify the relationship between the values in the target table and the values in the source table in the join clause. Then insert the row in the when not matched clause. And update them when matched.

The target table is the table whose rows you will add or change. You can merge source data into this. The source data must be a table, and the table returned by the query also has rows.

The Merge example is as follows, including several elements mentioned above. The source table is generated by a query, and the join key of the join clause happens to be the primary key to judge its uniqueness. In short, it is easy to understand:

merge into purchased_bricks pb
using ( 
  select 'blue' colour, 'cube' shape, 15.95 price 
  from   dual 
) bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price;
  
select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid       15.49
blue      cube          15.95

Merging Two Tables

If the source table is not a query, the merging of the two tables is also simple:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price;
  
select * from purchased_bricks;

commit;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid        9.99
blue      cube           7.75
red       cube           4.95

The following is the implementation without upsert, which is much more complex, but the logic is also clear:

update purchased_bricks pb
set    pb.price = (
  select bfs.price
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
)
where  exists (
  select null
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
);

insert into purchased_bricks ( 
  colour, shape, price
) 
  select bfs.colour, bfs.shape, bfs.price 
  from   bricks_for_sale bfs
  where  not exists (
    select null
    from   purchased_bricks pb
    where  pb.colour = bfs.colour 
    and    pb.shape = bfs.shape
  );

select * from purchased_bricks;

rollback;

Merge Restrictions

Merge also has restrictions. You can only modify:

  • Columns not in the join clause
  • Once per line

If the column in the join clause is modified, the error is reported as follows:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.colour = bfs.colour, pb.shape = bfs.shape;

Error at Command Line : 3 Column : 9
Error report -
SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "PB"."COLOUR"

If line 1 is modified multiple times (because the join condition changes), the error is as follows:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price;

Error report -
ORA-30926: unable to get a stable set of rows in the source tables

Conditional Merging

If you want to make conditional updates and inserts, you can add the where condition to the matched clause.

For example, the following SQL only supports rows whose color is blue:

update bricks_for_sale 
set    price = 100;

insert into bricks_for_sale values ( 'red', 'pyramid', 5.99 );

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
  where  bfs.colour = 'blue'
when matched then
  update set pb.price = bfs.price
  where  bfs.colour = 'blue';
  
select * from purchased_bricks;

rollback;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid         100
blue      cube            100
red       cube           4.95

Before merge, the data of the two tables are:

SQL> select * from bricks_for_sale;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube            100
blue      cube            100
blue      pyramid         100
red       pyramid        5.99

SQL> select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid        9.99
blue      cube           7.75
red       cube           4.95

Single Operation Merge

The when matched and when not matched clauses in merge are optional. Therefore, you can implement an insert only or update only merge. For example:

-- Insert only merge
merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price );

-- Updated only merge
merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when matched then
  update set pb.price = bfs.price;
  
select * from purchased_bricks;

rollback;

If you do not use merge, you can also use the following SQL to implement update only merge. But I'm sure you need to access bricks_ for_ The sale table twice, and the merge statement only once:

update purchased_bricks pb
set    pb.price = (
  select bfs.price
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
)
where  exists (
  select null
  from   bricks_for_sale bfs
  where  pb.colour = bfs.colour 
  and    pb.shape = bfs.shape
);

Merge + Delete

You can also use merge to delete rows from the target table, which only occurs when the rows in the target table have matching rows in the source table.
To do this, add a delete clause after the update in the when matched clause. For example:

when matched then
  update set pb.price = bfs.price
  delete where pb.colour = 'blue'

Deleting and using the values in the updated target table will only affect the existing rows, and the new rows in the merge operation will not be affected.

For example, the data before consolidation is as follows:

insert into bricks_for_sale values ( 'blue', 'cuboid', 5.99 );

select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
blue      pyramid         100
blue      cube            100
red       cube           4.95

select * from bricks_for_sale;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube            100
blue      cube            100
blue      pyramid         100
red       pyramid        5.99
blue      cuboid         5.99

Use the following SQL merge:

merge into purchased_bricks pb
using bricks_for_sale bfs
on    ( pb.colour = bfs.colour and pb.shape = bfs.shape )
when not matched then
  insert ( pb.colour, pb.shape, pb.price )
  values ( bfs.colour, bfs.shape, bfs.price )
when matched then
  update set pb.price = bfs.price
  delete where pb.colour = 'blue' ;
  
select * from purchased_bricks;

   COLOUR      SHAPE    PRICE
_________ __________ ________
red       cube            100
red       pyramid        5.99
blue      cuboid         5.99

rollback;

Environmental cleaning

drop table purchased_bricks;
drop table bricks_for_sale;

Tags: Database Oracle SQL Merge

Posted on Tue, 16 Nov 2021 11:24:25 -0500 by jstngk