A 'merge into' statement without using an alias?

Question

When I try to run a simple merge query, I've found that it fails if I don't set an aliases for the tables in the using statement: e.g.

create table table1test (col1 int not null,
                         col2 varchar2(255),
                         primary key(col1));

INSERT ALL
  INTO table1test (col1, col2) VALUES (1, '10')
  INTO table1test (col1, col2) VALUES (2, '20')
  INTO table1test (col1, col2) VALUES (3, '30')
SELECT * FROM dual;

create table table2test (col1 int not null,
                         col2 varchar2(255),
                         primary key(col1));

INSERT ALL
  INTO table2test (col1, col2) VALUES (1, 'a')
  INTO table2test (col1, col2) VALUES (2, 'b')
  INTO table2test (col1, col2) VALUES (3, 'c')
SELECT * FROM dual;

with the above tables, this works fine:

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

but this (i.e. just removing the alias for table2)

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

returns this error:

Error report:
SQL Error: ORA-00904: "TABLE2TEST"."COL1": invalid identifier
00904. 00000 -  "%s: invalid identifier"

Is this because it's really the checking against the using clause rather than the table? Or is there something else I've missed? The Oracle help page for USING doesn't state that a using clause has to have an alias.

[n.b. I couldn't create an SQLFiddle of this; the simple merge statement which works locally returns an ora-00900 error when I run it on there]

Answer 1

If you don't want to use the alias, then you could directly specify the table_name in the USING clause. You will need to use an alias to refer the column_names in the ON clause, SET clause or the WHERE clause.

The syntax is provided in the documentation(though not explicitly mentioned to use an alias for the subquery in the USING clause), however it goes with the SQL standard to use an alias for the subquery as you are not using a static table_name.

For example,

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

The USING clause now gives you the flexibility to refer any of the columns of table2.

However, to make it easy to read and understand, I would always qualify the columns in a subquery with an alias.

From documentation,

To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.

For example

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