for the following data, I'd like to be able to delete some rows and insert others giving the result below. Is this possible with a single statement (eg with the merge
statement)?
create table product(product_id integer primary key);
insert into product(product_id) values(1);
insert into product(product_id) values(2);
insert into product(product_id) values(3);
create table split( parent_id integer not null references product,
child_id integer not null references product,
primary key(parent_id, child_id) );
insert into split(parent_id, child_id) values(1,2);
insert into split(parent_id, child_id) values(1,3);
create table sale(sale_at date, product_id integer references product);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 2);
insert into sale(sale_at, product_id) values(sysdate, 2);
select sale_at, product_id from sale where product_id not in (select parent_id from split)
union all
select sale_at, child_id from sale join split on(parent_id=product_id);
/*
| SALE_AT | PRODUCT_ID |
--------------------------------------------
| June, 10 2013 15:18:22+0000 | 2 |
| June, 10 2013 15:18:22+0000 | 2 |
| June, 10 2013 15:18:22+0000 | 3 |
| June, 10 2013 15:18:22+0000 | 2 |
| June, 10 2013 15:18:22+0000 | 3 |
| June, 10 2013 15:18:22+0000 | 2 |
| June, 10 2013 15:18:22+0000 | 3 |
| June, 10 2013 15:18:22+0000 | 2 |
*/
—-edit:
to clarify, the effect I'm trying to get with a single merge
statement is this:
insert into sale(sale_at, product_id)
select sale_at, child_id from sale join split on parent_id=product_id;
delete from sale where product_id in (select parent_id from split);
and the reason I'd like a single statement solution is to prevent a race condition where another transaction inserts/commits data between the insert
and delete
in the primary transaction.
Best Answer
You can do it in one go, sort of:
This will delete the rows from sale that are present in split and replace them with their appropriate split products.
You could also write it:
Here the row in sale will be replaced (updated) by its first split component and the additional components will be inserted.