-
ORA-00904: error on merge statement
merge into aff_trial_source_label a
using (select name from affiliate_parent) p
on (a.source=p.name)
when matched then update set a.source=p.name
when not matched then insert(a.source_label_id,a.source,a.source_label) values(aff_trial_source_seq.nextval,p.name,p.name)
7 on (a.source=p.name)
*
ERROR at line 3:
ORA-00904: "A"."SOURCE": invalid identifier
the column name does exist in the table:
SQL> desc aff_trial_source_label
Name Null? Type
----------------------------------------- -------- ----------------------------
SOURCE_LABEL_ID NUMBER(13)
SOURCE VARCHAR2(512)
SOURCE_LABEL VARCHAR2(512)
any insight would be helpful.
thanks.
-
you can't use a varchar for the join in the "ON" clause or it bombs. Create a numeric join field or join on a numeric and you'll be ok.
-
You can use a varchar in your join condition. Source is a reserved word.
SQL> create table t1(id number, name varchar2(10), source varchar2(10));
Table created.
SQL>
SQL> create table t2(id number, name varchar2(10), source varchar2(10));
Table created.
SQL>
SQL> merge into t1 a
2 using (select source, name from t2) b
3 on (a.SOURCE = b.name)
4 when matched then update set a.source = b.name
5 when not matched then insert (source, name)
6 values (b.name, b.source);
on (a.SOURCE = b.name)
*
ERROR at line 3:
ORA-00904: invalid column name
SQL>
SQL> merge into t1 a
2 using (select source, name from t2) b
3 on (a.name = b.name)
4 when matched then update set a.source = b.name
5 when not matched then insert (source, name)
6 values (b.name, b.source);
0 rows merged.
SQL> select *
2 from v$reserved_words
3 where keyword = 'SOURCE';
KEYWORD
-------------------------------
LENGTH
----------
SOURCE
6
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|