sqlfiddle2 icon indicating copy to clipboard operation
sqlfiddle2 copied to clipboard

Merge statements not executing

Open wood-chris opened this issue 9 years ago • 0 comments

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

wood-chris avatar Jun 02 '15 15:06 wood-chris