Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
*/

(SQLFiddle)

----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.

share|improve this question
    
Jack, why do you need this? is only for your personal knowledge or there is business constrain? –  Ste Jun 11 '13 at 7:27
1  
It's a simplified example of a real business requirement. I think I can solve the problem with separate statements and 'serializable' –  Jack Douglas Jun 11 '13 at 9:06
1  
What table do you want to insert into? –  Vincent Malgrat Jun 11 '13 at 12:58
    
Jack, I think we'll need some more explanation. Are you trying to make the sale table look like the results of your union? Is split just a table you are creating to show us what the transformed results should look like or is it part of the structure that should make the change? Perhaps if you showed us the separate insert and delete statements your are trying to... err... merge, it would help us understand the process. –  Leigh Riffel Jun 11 '13 at 13:47
2  
An attempt that fails: SQL-Fiddle (if you uncomment the commented line) –  ypercube Jun 11 '13 at 14:18
show 3 more comments

1 Answer

up vote 8 down vote accepted

You can do it in one go, sort of:

SQL> MERGE INTO sale s
  2  USING (SELECT ROWID rid, sale_at, product_id
  3           FROM sale
  4          WHERE product_id IN (SELECT parent_id FROM split)
  5         UNION ALL
  6         SELECT CAST (NULL AS ROWID) rid, sale_at, child_id FROM sale
  7           JOIN split ON (parent_id = product_id)) m
  8  ON (s.ROWID = m.rid)
  9  WHEN MATCHED THEN
 10     UPDATE SET s.sale_at = m.sale_at
 11     DELETE WHERE 1 = 1
 12  WHEN NOT MATCHED THEN
 13     INSERT VALUES (m.sale_at, m.product_id);

Done

SQL> select * from sale;

SALE_AT                                  PRODUCT_ID
----------- ---------------------------------------
11/06/2013                                        2
11/06/2013                                        2
11/06/2013                                        3
11/06/2013                                        3
11/06/2013                                        3
11/06/2013                                        2
11/06/2013                                        2
11/06/2013                                        2

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:

SQL> MERGE INTO sale s
  2  USING (SELECT CASE WHEN row_number() over (PARTITION BY s.rowid
  3                                             ORDER BY sp.rowid) = 1
  4                     THEN  s.rowid
  5                END rid,
  6                s.sale_at, sp.parent_id, sp.child_id
  7           FROM split sp
  8           JOIN sale s
  9             ON sp.parent_id = s.product_id) m
 10  ON (s.rowid = m.rid)
 11  WHEN MATCHED THEN UPDATE SET s.product_id = m.child_id
 12  WHEN NOT MATCHED THEN INSERT VALUES (m.sale_at, m.child_id);

Here the row in sale will be replaced (updated) by its first split component and the additional components will be inserted.

share|improve this answer
    
This is awesome, thanks. It's working perfectly for my real-world problem, the only thing I've added is explicit column names in the WHEN NOT MATCHED THEN INSERT sub-clause –  Jack Douglas Jun 13 '13 at 13:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.