主页 / dba / 问题 / 44215
Accepted
Jack Douglas
Asked: 2013-06-11 07:25:19 +0800 CST

如何在一个语句中组合“删除”和“插入”操作？

  • 772

对于以下数据，我希望能够删除一些行并插入其他行，给出以下结果。这是否可以通过单个语句（例如merge语句）来实现？

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 )

- - 编辑：

澄清一下，我试图用一个单一的merge语句得到的效果是这样的：

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);

我想要一个单语句解决方案的原因是为了防止另一个事务在主事务insert之间插入/提交数据的竞争条件。delete

oracle oracle-11g-r2

1 个回答

  1. Best Answer

    你可以一口气完成，有点：

    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

    这将从销售中删除拆分中存在的行，并将其替换为相应的拆分产品。

    你也可以这样写：

    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);

    在这里，销售中的行将被其第一个拆分组件替换（更新），并且将插入其他组件。

    • 10

相关问题