查看: 11002|回复: 27

【话题讨论】这个UPDATE语句,如何改成MERGE不?

[复制链接]
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2014-6-5 09:13 | 显示全部楼层 |阅读模式
ORACLE 11203

update t_so_line_ty slt set prod_so_status= (select status from t_sale_order so where slt.ref_id = so.pk_sales_id and so.status in (11)) where prod_so_status is null;
update t_so_line_ty slt set prod_so_status= (select status from t_sale_order so where slt.ref_id = so.pk_sales_id and so.status in (12)) where prod_so_status is null;

不是执着,而是探讨ERGE语句的优劣势。



求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
发表于 2014-6-5 09:18 | 显示全部楼层
本帖最后由 moseslin 于 2014-6-5 09:19 编辑

merge into t_so_line_ty slt
using (select * from t_sale_order) so
on (slt.ref_id = so.pk_sales_id and so.status in(11, 12) and slt.prod_so_status is null)
when matched then
  update set prod_so_status = so.status

是不是这样?

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
 楼主| 发表于 2014-6-5 09:24 | 显示全部楼层
moseslin 发表于 2014-6-5 09:18
merge into t_so_line_ty slt
using (select * from t_sale_order) so
on (slt.ref_id = so.pk_sales_id  ...

YQ@oeldzdz>explain plan for
  2   merge into t_so_line_ty slt using t_sale_order so on (slt.prod_so_status is null and slt.ref_id = so.pk_sales_id and so.status in (11, 12))
  3    when matched then update set slt.prod_so_status = so.status;
merge into t_so_line_ty slt using t_sale_order so on (slt.prod_so_status is null and slt.ref_id = so.pk_sales_id and so.status in (11, 12))
                                                       *
ERROR at line 2:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "SLT"."PROD_SO_STATUS"

使用道具 举报

回复
认证徽章
论坛徽章:
17
生肖徽章2007版:猴
日期:2015-07-24 10:50:33紫水晶
日期:2015-09-14 19:29:07萤石
日期:2015-09-14 19:24:48萤石
日期:2015-09-13 14:30:02萤石
日期:2015-09-11 23:05:02红宝石
日期:2015-09-11 23:04:43萤石
日期:2015-09-11 23:04:27生肖徽章2007版:兔
日期:2015-07-31 16:43:10生肖徽章2007版:龙
日期:2015-07-24 10:51:00生肖徽章2007版:龙
日期:2015-07-24 10:50:51
发表于 2014-6-5 09:25 | 显示全部楼层
MERGE INTO t_so_line_ty slt   
USING  (select * from  t_sale_order where  status in (11,12))  so  
  ON (slt.ref_id = so.pk_sales_id )   
   WHEN MATCHED THEN  
  UPDATE  
    SET slt.prod_so_status = so. status
  WHERE slt. prod_so_status  is  null;

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2014-6-5 09:42 | 显示全部楼层
本帖最后由 bell6248 于 2014-6-5 09:46 编辑


原来的2句语句写的很奇怪, 测试一下如下是否等效

merge into t_so_line_ty a
using (select slt.rowid rid,
              so.status
         from t_so_line_ty slt,
              t_sale_order so
        where slt.ref_id = so.pk_sales_id
          and so.status in (11, 12)
          and slt.prod_so_status is null) b
on (a.rowid = b.rid)
when matched then
  update set a.prod_so_status = b.status

使用道具 举报

回复
认证徽章
论坛徽章:
284
生肖徽章2007版:虎
日期:2014-06-23 20:17:04生肖徽章2007版:虎
日期:2014-09-18 12:50:09生肖徽章2007版:虎
日期:2014-11-14 14:21:28生肖徽章2007版:虎
日期:2014-06-23 20:42:17生肖徽章2007版:虎
日期:2014-06-23 20:42:17秀才
日期:2017-04-06 13:42:06秀才
日期:2017-04-06 13:41:23秀才
日期:2017-03-28 15:59:38秀才
日期:2017-03-28 15:11:09红宝石
日期:2017-04-06 13:47:15
发表于 2014-6-5 09:50 | 显示全部楼层
2楼和四楼的改写 未必等价吧?
对于update来说,只要满足 prod_so_status is null 都需要更新。
对于merge来说,除了满足 prod_so_status is null,还需要满足slt.ref_id = so.pk_sales_id才能更新。

使用道具 举报

回复
论坛徽章:
55
山治
日期:2017-01-03 16:19:442014年新春福章
日期:2014-05-15 10:20:51马上有钱
日期:2014-06-19 14:11:34路虎
日期:2014-06-19 14:11:34马上加薪
日期:2014-06-19 14:11:342014年新春福章
日期:2014-06-19 14:11:34马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:08阿斯顿马丁
日期:2014-06-19 14:11:34问答徽章
日期:2013-11-21 09:21:29
发表于 2014-6-5 09:55 | 显示全部楼层
版主的两条语句会更新成怎样的结果呢?觉得很奇怪。

1楼两条语句不就等价于下面的:
update t_so_line_ty slt set prod_so_status= 11 where prod_so_status is null;
update t_so_line_ty slt set prod_so_status= 12 where prod_so_status is null;

究竟是想把prod_so_status更新成11呢还是12?

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
 楼主| 发表于 2014-6-5 09:56 | 显示全部楼层
hh7yx 发表于 2014-6-5 09:50
2楼和四楼的改写 未必等价吧?
对于update来说,只要满足 prod_so_status is null 都需要更新。
对于merg ...

4楼我感觉是对的,但未必高效。

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
 楼主| 发表于 2014-6-5 10:01 | 显示全部楼层
bell6248 发表于 2014-6-5 09:42
原来的2句语句写的很奇怪, 测试一下如下是否等效

merge into t_so_line_ty a

为何你的语句没改任何记录?

YQ@oeldzdz>set autot trace exp stat;
YQ@oeldzdz>  update t_so_line_ty slt set prod_so_status= (select status from t_sale_order so where slt.ref_id = so.pk_sales_id and so.status in (11,12)) where prod_so_status is null;

350384 rows updated.

Elapsed: 00:00:07.44

Execution Plan
----------------------------------------------------------
Plan hash value: 2263494101

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                     |   349K|  6488K|  1405K  (1)| 00:59:43 |
|   1 |  UPDATE                      | T_SO_LINE_TY        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL          | T_SO_LINE_TY        |   349K|  6488K|  6755   (3)| 00:00:18 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| T_SALE_ORDER        |     1 |    24 |     4   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T_SALE_ORDER_01 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PROD_SO_STATUS" IS NULL)
   3 - filter(TO_NUMBER("SO"."STATUS")=12 OR TO_NUMBER("SO"."STATUS")=11)
   4 - access("SO"."PK_SALES_ID"=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
      43588  db block gets
     226785  consistent gets
       2113  physical reads
   37121772  redo size
        569  bytes sent via SQL*Net to client
        855  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     350384  rows processed

YQ@oeldzdz>roll;
Rollback complete.
YQ@oeldzdz>merge into t_so_line_ty a
  2  using (select slt.rowid rid,
  3                so.status
  4           from t_so_line_ty slt,
  5                t_sale_order so
  6          where slt.ref_id = so.pk_sales_id
  7            and so.status in (11, 12)
  8            and slt.prod_so_status is null) b
  9  on (a.rowid = b.rid)
10  when matched then
11    update set a.prod_so_status = b.status
12  /

0 rows merged.

Elapsed: 00:00:01.36

Execution Plan
----------------------------------------------------------
Plan hash value: 2551936746

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT       |              |   257K|  2260K|       | 38954   (3)| 00:01:40 |
|   1 |  MERGE                | T_SO_LINE_TY |       |       |       |            |          |
|   2 |   VIEW                |              |       |       |       |            |          |
|*  3 |    HASH JOIN          |              |   257K|    71M|    16M| 38954   (3)| 00:01:40 |
|*  4 |     HASH JOIN         |              |   257K|    13M|    10M| 18380   (3)| 00:00:47 |
|*  5 |      TABLE ACCESS FULL| T_SO_LINE_TY |   257K|  7785K|       |  6757   (3)| 00:00:18 |
|*  6 |      TABLE ACCESS FULL| T_SALE_ORDER |   642K|    14M|       |  9967   (2)| 00:00:26 |
|   7 |     TABLE ACCESS FULL | T_SO_LINE_TY |  1086K|   245M|       |  6880   (5)| 00:00:18 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A".ROWID="SLT".ROWID)
   4 - access("SLT"."REF_ID"="SO"."PK_SALES_ID")
   5 - filter("SLT"."PROD_SO_STATUS" IS NULL AND "SLT"."REF_ID" IS NOT NULL)
   6 - filter(TO_NUMBER("SO"."STATUS")=12 OR TO_NUMBER("SO"."STATUS")=11)


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
      90305  consistent gets
          0  physical reads
          0  redo size
        569  bytes sent via SQL*Net to client
       1030  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

YQ@oeldzdz>

使用道具 举报

回复
认证徽章
论坛徽章:
284
生肖徽章2007版:虎
日期:2014-06-23 20:17:04生肖徽章2007版:虎
日期:2014-09-18 12:50:09生肖徽章2007版:虎
日期:2014-11-14 14:21:28生肖徽章2007版:虎
日期:2014-06-23 20:42:17生肖徽章2007版:虎
日期:2014-06-23 20:42:17秀才
日期:2017-04-06 13:42:06秀才
日期:2017-04-06 13:41:23秀才
日期:2017-03-28 15:59:38秀才
日期:2017-03-28 15:11:09红宝石
日期:2017-04-06 13:47:15
发表于 2014-6-5 10:02 | 显示全部楼层
ZALBB 发表于 2014-6-5 09:56
4楼我感觉是对的,但未必高效。

用例子 推倒 四楼
create table t_11 as (select 1 a,'aaa' b,null c from dual union all select 2,'bbb',null  from dual union all select 3,'c',1  from dual);
create table t_12 as (select 1 a,'aaa' b,null c from dual union all select 2,'bbb',null  from dual union all select 3,'c',1  from dual);

create table t_2 as (select 1 a,'aa' b,null c from dual union all select 3,'cc',1  from dual);


UPDATE t_11
   SET b =
       (SELECT b FROM t_2 WHERE t_11.a = t_2.a)
WHERE t_11.c IS NULL;

MERGE INTO t_12
USING (SELECT a,b FROM t_2) t_2
ON (t_12.a = t_2.a)
WHEN MATCHED THEN
  UPDATE SET t_12.b = t_2.b
WHERE t_12.c IS NULL;

select * from t_11;
select * from t_12;

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表