What is the syntax for merge-delete target rows with filter?

I’m learning SQL on Oracle Dev Gym. I’m taking the class Databases for Developers: Next Level: Merge, by Chris Saxon.

I can’t for the life of me figure out how to delete rows with filter in the destination table. Even the example given doesn’t work. I looked at a few other different tutorials and they don’t work either.

Here is the schema:

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;

Here is the example code that is supposed to (1) insert a new row into bricks_for_sale bfs, (2) merge bfs into purchased_bricks pb, and (3) remove the existing blue bricks from pb. It’s copied-and-pasted from the tutorial so it should work.

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

select * from purchased_bricks;

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;

rollback;

The result is

|COLOUR|SHAPE  |PRICE|
|------|-------|-----|
|red   |cube   |4.95 |
|blue  |cuboid |5.99 |
|blue  |cube   |7.75 |
|blue  |pyramid|9.99 |

Is this the expected behavior? I knew the blue-cuboid-5.99 row would remain because it’s not in the source table. However, shouldn’t the blue-cube/-pyramid rows be gone? It looks like the delete clause isn’t doing anything.

I’ve been looking at other merge-delete tutorials and still can’t figure out how to use merge-delete or how it’s supposed to work.

Answer

The delete clause is a little odd, but it just lets you do extra stuff during your merge operation. It can go in the insert clause, the update clause, or both. The key to using it is to realize it only operates on rows that match the ON clause.

In your case purchased_bricks is empty at the start, so nothing matches the ON clause and everything goes through the insert clause. Therefore, the delete clause has no effect.

You can see this in operation if you don’t do the rollback and run the merge statement a second time.

After running the merge once:

select * from purchased_bricks;

COLOUR     SHAPE           PRICE
---------- ---------- ----------
blue       pyramid          9.99
blue       cube             7.75
red        cube             4.95
blue       cuboid           5.99

Now run it a second time and you get:

merge into [...]

4 rows merged.

select * from purchased_bricks;

COLOUR     SHAPE           PRICE
---------- ---------- ----------
red        cube             4.95

Since we had a blue cube, pyramid, and cuboid in the bricks_for_sale table, they all matched in the on clause, everything went through the merge clause, and the blue ones were deleted.

From the documentation:

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET … WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.