sqlfiddle2
sqlfiddle2 copied to clipboard
Merge statements not executing
On my version of Oracle:
SELECT * FROM V$VERSION;
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
I can run the following code successfully:
create table table1t (col1 int not null,
col2 varchar2(255),
primary key(col1));
INSERT ALL
INTO table1t (col1, col2) VALUES (1, '10')
INTO table1t (col1, col2) VALUES (2, '20')
INTO table1t (col1, col2) VALUES (3, '30')
SELECT * FROM dual;
create table table2t (col1 int not null,
col2 varchar2(255),
primary key(col1));
INSERT ALL
INTO table2t (col1, col2) VALUES (1, 'a')
INTO table2t (col1, col2) VALUES (2, 'b')
INTO table2t (col1, col2) VALUES (3, 'c')
SELECT * FROM dual;
select * from table1t;
merge into table1t
using (select col1 from table2t) test2 on (table1t.col1 = test2.col1)
when matched
then update
set table1t.col2 = 'hello';
select * from table1t;
The first select
statements produce the following output:
1 10
2 20
3 30
and then, after running the merge, the second select
statement produces:
1 hello
2 hello
3 hello
which seems to be the correct behaviour. However, running the merge
statement in SQLfiddle (on Oracle 11g R2) produces the following error:
ORA-00900: invalid SQL statement