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;When adding rows to a table, sometimes you want to do insert-if-not-exists, update-if-exists logic. Aka an upsert. Writing this as separate insert and update statements is cumbersome.
For example, say you want to upsert a blue pyramid into the table of purchased_bricks. If it exists, you want to change its price. And if it doesn't, you want to add it.
You can do this using the following code block. The attribute sql%rowcount returns the number of rows affected by the last statement. You can use this to see if an update changed any rows. If it returns zero, it did nothing. So the row doesn't exist and you need to insert it:
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;Purchased_bricks was empty before this code. So the update was wasted effort. It would have been better to do the insert first.
You can do this by flipping the statements around. And, instead of checking if the sql%rowcount is zero, trapping a unique key violation:
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;But the code above "added" the same brick! You needed to update its price. So the insert was a waste here.
When writing upserts, in general it's hard to know whether insert or update is most likely. You can spend a lot of effort figuring this out. Luckily there's a better way: Merge!
Merge is one statement that allows you to do either an insert or an update as needed. To use it, you need to state how values in the target table relate to those in the source in the join clause. Then add rows in the when not matched clause. And update them using when matched.
The target table is the one that you'll add or change the rows of. You merge the source data into this.
The source has to be a table. But remember: a query returns a table! So you can select the values you want to upsert. So you can merge a blue cube costing 15.95 by querying these values like so:
select 'blue' colour, 'cube' shape, 15.95 price from dual
You then need to link these values to rows in the target. Each source row should each link to at most one row in the target table. The primary key of purchased_bricks is colour and shape. So you can guarantee this by joining using these columns:
on ( pb.colour = bfs.colour and pb.shape = bfs.shape )
You then define what to add or change in merge's matched clauses.
This clause fires for each row in the source that links to a row in the target. So if there is a blue cube in the target table, you can change its price here, like so:
when matched then update set pb.price = bfs.price;
For each row in the source that doesn't match one in the target, the when not matched clause fires. Here you state the values you'd like to insert into the target for which columns. If the source and target tables have the same column names, you must alias the columns in the values clause:
when not matched then insert ( pb.colour, pb.shape, pb.price ) values ( bfs.colour, bfs.shape, bfs.price )
Putting together this gives the following statement to merge a blue cube:
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;Note you can place the matched clauses in either order. Whether you do "when match" or "when not matched" first is a matter of personal preference.
You may also want to upsert two whole tables, so all the rows in the source have a matching row in the target.
You can do this by writing an update-if-exists. Followed by an insert-if-not-exists (or vice-versa), like so:
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;But, as with upserting a single row, this a lot of typing and hard to follow. You can simplify the above into the following 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;
select * from purchased_bricks;
commit;This allows you to keep target rows in sync with those in the source. But this only affects rows with a match in the source.
For example, if you:
Merge adds the red pyramid. But it will only change the price of the red cube and blue bricks. The green cube has no matching row in bricks_for_sale. So its price stays the same:
update bricks_for_sale
set price = 0.99;
insert into bricks_for_sale values ( 'red', 'pyramid', 5.99 );
insert into purchased_bricks values ( 'green', 'cube', 9.95 );
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;
rollback;Replace the /* TODO */ sections below to complete the following merge. It should add the yellow cube to purchased_bricks. And update the price of the red brick to 5.55:
merge into purchased_bricks pb
using (
select 'yellow' colour, 'cube' shape, 9.99 price from dual
union all
select 'red' colour, 'cube' shape, 5.55 price from dual
) bfs
on ( /* TODO */ )
when not matched then
insert /* TODO */
when matched then
update /* TODO */;
select * from purchased_bricks
order by colour, shape;
rollback;The query should return the following rows:
COLOUR SHAPE PRICE blue cube 7.75 blue pyramid 9.99 red cube 5.55 yellow cube 9.99
There are a couple of things you need to watch for in the when matched clause. You can only update:
If you try to set columns in the join clause, you'll get an error. For example, the following fails because it tries to update colour and shape, which are in the join clause:
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;The join should map each row in the source to at most one in the target. If there are two or more rows in the source which map to a row in the target, you'll get an error.
For example, the following joins the tables on colour alone. There are two rows in the source with the colour blue. So this will try and change blue rows in the target twice. This leads to an error:
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;When merging tables, you may want to preserve values for some of the target's existing rows. Or stop users inserting certain new values.
You can do both of these using a where clause in the matched clauses. This is like a regular where clause and comes after the insert or update, e.g.:
insert ( pb.colour, pb.shape, pb.price ) values ( bfs.colour, bfs.shape, bfs.price ) where bfs.colour = 'blue'
For example, the following code updates the price of all the bricks for sale to 100 and adds red pyramid. But the merge includes a filter to only affect blue rows in both clauses. So the price of the red cube remains 4.95 and the red pyramid is not added to purchased_bricks:
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;Complete the where clauses in the merge statement below, so that it:
update bricks_for_sale
set price = 49.99;
insert into bricks_for_sale values ( 'red', 'pyramid', 5.99 );
insert into bricks_for_sale values ( 'green', '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 /* TODO */
when matched then
update set pb.price = bfs.price
where /* TODO */;
select * from purchased_bricks
order by colour, shape;
rollback;After running all the code above (update, inserts, and completed merge) the output of the query on purchased_bricks should be:
COLOUR SHAPE PRICE blue cube 49.99 blue pyramid 9.99 green pyramid 5.99 red cube 49.99
Both the when matched and when not matched clauses of merge are optional. So you can have insert only or update only merges.
For example, you could split the previous merges into two:
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 );
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;Separating merges like this is a bad idea. In general you should combine SQL into as few statements as possible.
But an update only merge can be useful in some cases. Say you want to update the price of all the purchased_bricks with their current sale price. You can use the correlated update from part 4 of this tutorial:
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 );
You need the where exists clause to ensure you only update colours and shapes in both tables. Without this, if there you have bricks which are no longer for sale, the subquery in the set clause will return nothing. So the database will set the price of these to null!
This means the update will have to access bricks_for_sale at least twice. And it'll run the subquery to get the price once for each row in purchased_bricks. Whereas merge can access bricks_for_sale exactly once. This can be a big saving compared to the correlated update. So there can be situations where an update only merge is handy.
You can also use merge to remove rows from the target table. This will only happen for rows in the target that have a matching row in the source.
To do this, add a delete clause after the update in the when matched clause. For example, to remove the matching blue rows, use:
when matched then update set pb.price = bfs.price delete where pb.colour = 'blue'
The delete uses values from the target table after applying the update.
Remember: this only changes existing rows. If the merge adds a new row that meets the criteria, it remains in the target table.
For example, at this point purchased_bricks should have two blue rows in it. If you delete rows with the colour blue in the when matched clause, only these are removed.
You can insert another blue brick into bricks_for_sale, which the merge will add. This remains in purchased_bricks after the merge completes, even though the delete removes blue rows:
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;Complete the following merge statement, so it removes matched rows from purchased_bricks that have a price less than 9:
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 /* TODO */ ;
select * from purchased_bricks;
rollback;After the merge completes, the query of purchased_bricks should return the following rows:
COLOUR SHAPE PRICE blue pyramid 9.99