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

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