oracle merge into ora 00905,oracle 10g merge命令的增强

àì夳堔傛蜴生んèń 2022-10-21 01:08 214阅读 0赞

merge into 语句写法主要功能是对两个表进行关联,如果存在相等值就更新,

不存在就插入

先看9i写法:

create table test (id number,name varchar2(20));

create table paul (id number,name varchar2(50));

insert into test values(10,'abcd');

insert into paul values(5,'def');

insert into test values(1,'right');

insert into paul values(1,'false');

SQL> select \* from paul;

ID NAME

\---------- --------------------------------------------------

5 def

1 false

SQL> select \* from test;

ID NAME

\---------- --------------------

10 abcd

1 right

SQL>

SQL> MERGE INTO test a

2    USING paul b

3      ON (a.id = b.id)

4    WHEN MATCHED THEN

5      UPDATE SET a.name = b.name

6    WHEN NOT MATCHED THEN

7      INSERT (id, name)

8      VALUES (b.id, b.name)

9  ;

Done

SQL> select \*  from test;

ID NAME

\---------- --------------------

10 abcd

1 false

5 def

SQL>

SQL> MERGE INTO test a

2    USING paul b

3      ON (a.id = b.id)

4   WHEN NOT MATCHED THEN

5      INSERT (id, name)

6      VALUES (b.id, b.name);

MERGE INTO test a

USING paul b

ON (a.id = b.id)

WHEN NOT MATCHED THEN

INSERT (id, name)

VALUES (b.id, b.name)

ORA-00905: missing keyword

SQL>

SQL> MERGE INTO test a

2    USING paul b

3      ON (a.id = b.id)

4    WHEN MATCHED THEN

5      UPDATE SET a.name = b.name;

MERGE INTO test a

USING paul b

ON (a.id = b.id)

WHEN MATCHED THEN

UPDATE SET a.name = b.name

ORA-00905: missing keyword

可以看出在9i中对单一的进行insert 或update 分开是不行的

10g 上就可以了:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL>

SQL> create table test (id number,name varchar2(20));

Table created

SQL> create table paul (id number,name varchar2(50));

Table created

SQL> insert into test values(10,'abcd');

1 row inserted

SQL> insert into paul values(5,'def');

1 row inserted

SQL> insert into test values(1,'right');

1 row inserted

SQL> insert into paul values(1,'false');

1 row inserted

SQL> commit;

Commit complete

SQL> select \* from paul;

ID NAME

\---------- --------------------------------------------------

5 def

1 false

SQL> select \* from test;

ID NAME

\---------- --------------------

10 abcd

1 right

SQL>

SQL> MERGE INTO test a

2    USING paul b

3      ON (a.id = b.id)

4    WHEN MATCHED THEN

5      UPDATE SET a.name = b.name

6    WHEN NOT MATCHED THEN

7      INSERT (id, name)

8      VALUES (b.id, b.name)

9  ;

Done

SQL> select \* from test;

ID NAME

\---------- --------------------

10 abcd

1 false

5 def

SQL> rollback;

Rollback complete

SQL>

单独的insert

SQL> MERGE INTO test a

2    USING paul b

3      ON (a.id = b.id)

4   WHEN NOT MATCHED THEN

5      INSERT (id, name)

6      VALUES (b.id, b.name);

Done

SQL> select \* from test;

ID NAME

\---------- --------------------

10 abcd

1 right

5 def

SQL> rollback;

Rollback complete

单独的update

SQL>

SQL> MERGE INTO test a

2    USING paul b

3      ON (a.id = b.id)

4    WHEN MATCHED THEN

5      UPDATE SET a.name = b.name;

Done

SQL> select \* from test;

ID NAME

\---------- --------------------

10 abcd

1 false

还可以增加where 条件和delete条件

Conditional Operations

Conditional inserts and updates are now possible by using a WHERE clause on these statements.

\-- Both clauses present.

MERGE INTO test1 a

USING all\_objects b

ON (a.object\_id = b.object\_id)

WHEN MATCHED THEN

UPDATE SET a.status = b.status

WHERE b.status != 'VALID'

WHEN NOT MATCHED THEN

INSERT (object\_id, status)

VALUES (b.object\_id, b.status)

WHERE b.status != 'VALID';

\-- No matched clause, insert only.

MERGE INTO test1 a

USING all\_objects b

ON (a.object\_id = b.object\_id)

WHEN NOT MATCHED THEN

INSERT (object\_id, status)

VALUES (b.object\_id, b.status)

WHERE b.status != 'VALID';

\-- No not-matched clause, update only.

MERGE INTO test1 a

USING all\_objects b

ON (a.object\_id = b.object\_id)

WHEN MATCHED THEN

UPDATE SET a.status = b.status

WHERE b.status != 'VALID';

DELETE Clause

An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.

MERGE INTO test1 a

USING all\_objects b

ON (a.object\_id = b.object\_id)

WHEN MATCHED THEN

UPDATE SET a.status = b.status

WHERE b.status != 'VALID'

DELETE WHERE (b.status = 'VALID');

oracle merge into ora 00905,oracle 10g merge命令的增强

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle MERGE INTO

相关 Oracle中merge into的使用

相关 oracle Merge Into 用法

相关 oracle merge into ora 00905,oracle 10g merge命令的增强

相关 Oracle merge into delete语法

相关 Oracle语法：merge into using

相关 Oracle之merge into 解析

相关 Oracle中merge into的使用

相关 oracle merge into 用法详解

相关 Oracle中merge into的使用