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;