oracle如何把update(含max函数的语句)改为merge into呢

oracle怎么把update(含max函数的语句)改为merge into呢？
创建数据库：

C# code

create table DATA_MODEL_FINAL(  OBJID           NUMBER not null,  OBJUSERID       VARCHAR2(20),  OBJELEVATION    FLOAT,  GROUPID         VARCHAR2(20) default 0);create bitmap index GROUPID_model_index on data_model_final(GROUPID);create unique index objuserid_index on data_model_final(objuserid);  create table DATA_PIPE_FINAL(  OBJID              NUMBER not null,  STARPOINTELEVATION FLOAT,  GROUPID            VARCHAR2(20) default 0,  START_POINT        VARCHAR2(20));create index START_POINT_index on data_pipe_final(START_POINT);create bitmap index GROUPID_pipe_index on data_pipe_final(GROUPID);insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910615,'AJ31123',0,'0201');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910616,'AJ31124',0,'0202');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910617,'AJ31125',0,'0203');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910618,'AJ31126',0,'0204');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910619,'AJ31127',0,'0204');insert into DATA_MODEL_FINAL (OBJID,OBJUSERID,OBJELEVATION,GROUPID) values (1910620,'AJ31128',0,'0204');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201101,'AJ31126',1.5,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201102,'B000001',1.8,'0101');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201103,'AJ31126',2.2,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201104,'AJ31126',1.5,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201105,'AJ31127',1.5,'0104');insert into data_pipe_final (OBJID,START_POINT,STARPOINTELEVATION,GROUPID) values (201106,'AJ31127',1.9,'0104');

update语句：

SQL code

        UPDATE DATA_MODEL_FINAL A SET OBJELEVATION=(SELECT MAX(B.STARPOINTELEVATION) FROM DATA_PIPE_FINAL B           WHERE B.GROUPID='0104' AND B.START_POINT=A.OBJUSERID           ) WHERE A.GROUPID='0204';

以上数据库是我精简了很多字段和数据的，实际上这2个表的数据都超过50万条了，使用update很影响效率，因此我想把以上update语句改为merge into以提高效率，各位有什么好方法否？

SQL code

    merge into DATA_MODEL_FINAL A         using DATA_PIPE_FINAL B           on (B.GROUPID='0104' AND A.GROUPID='0204' AND B.START_POINT=A.OBJUSERID)            when matched then          update set A.OBJELEVATION=MAX(B.STARPOINTELEVATION)

[解决办法]

SQL code

 merge into DATA_MODEL_FINAL A         using (SELECT MAX(STARPOINTELEVATION) as STARPOINTELEVATION, START_POINT FROM DATA_PIPE_FINAL               WHERE GROUPID='0104'                group by START_POINT) b    on (A.GROUPID='0204' AND B.START_POINT=A.OBJUSERID)            when matched then          update set A.OBJELEVATION=MAX(B.STARPOINTELEVATION) 

 

查看更多 下一篇