sql - A 'merge into' statement without using an alias? -

when try run simple merge query, i've found fails if don't set aliases tables in using statement: e.g.

create table table1test (col1 int not null,                          col2 varchar2(255),                          primary key(col1));  insert   table1test (col1, col2) values (1, '10')   table1test (col1, col2) values (2, '20')   table1test (col1, col2) values (3, '30') select * dual;  create table table2test (col1 int not null,                          col2 varchar2(255),                          primary key(col1));  insert   table2test (col1, col2) values (1, 'a')   table2test (col1, col2) values (2, 'b')   table2test (col1, col2) values (3, 'c') select * dual; 

with above tables, works fine:

merge table1    using (select col1 table2) test2 on (table1.col1 = test2.col1) when matched update set table1.col2 = 'hello'; 

but (i.e. removing alias table2)

merge table1    using (select col1 table2) on (table1.col1 = table2.col1) when matched update set table1.col2 = 'hello'; 

returns error:

error report: sql error: ora-00904: "table2test"."col1": invalid identifier 00904. 00000 -  "%s: invalid identifier" 

is because it's checking against using clause rather table? or there else i've missed? oracle page using doesn't state using clause has have alias.

[n.b. couldn't create sqlfiddle of this; simple merge statement works locally returns ora-00900 error when run on there]

if don't want use alias, directly specify table_name in using clause. need use alias refer column_names in on clause, set clause or where clause.

the syntax provided in documentation(though not explicitly mentioned use alias subquery in using clause), goes sql standard use alias subquery not using static table_name.

for example,

merge table1    using table2  on (table1.col1 = table2.col1) when matched update set table1.col2 = 'hello'; 

the using clause gives flexibility refer of columns of table2.

however, make easy read , understand, qualify columns in subquery alias.

from documentation,

to make statements easier read, qualify columns in subquery name or alias of table, view, or materialized view.

for example

merge table1 t1   using table2 t2 on (t1.col1 = t2.col1) when matched update set t1.col2 = 'hello';