Column ambiguously defined error with Oracle Merge statement

Question

I have a problem with this query in Oracle SQL.

Errore alla riga del comando:18 Colonna:42
Report errori:
Errore SQL: ORA-00918: column ambiguously defined
00918. 00000 - "column ambiguously defined"
*Cause:
*Action:

This is the query, but i don't see the column ambiguously defined:

MERGE INTO T_HPSM_CM_UBIS H USING
(SELECT A.itam_relevant_appl_code as ret,
 b.service_id,
 b.it_service,
 b.itam_user_id,
 b.itam_last_name,
 b.itam_first_name,
 b.itrm_user_id,
 b.itam_first_name,
 b.itam_last_name
FROM
 (SELECT f.itam_relevant_appl_code
  FROM T_HPSM_CM_UBIS f
  GROUP BY f.itam_relevant_appl_code
 ) A,
 t_cp_list_itam b
 WHERE A.itam_relevant_appl_code   = b.hvb_appl_code
) s ON (H.itam_relevant_appl_code = s.ret)
WHEN MATCHED THEN
UPDATE SET   H.service_id = s.service_id, H.it_service = s.it_service;

i think the problem is with "s.ret", you also have a "ret" as an alias on the 2nd line of your MERGE statement — davegreen100
maybe try writing a JOIN statement between A and t_cp_list_itam b — Jeremy C.
Why columns b.itam_last_name and b.itam_first_name selected twice with the same name? — San

Boneist Boneist · Accepted Answer · 2015-06-15T13:20:41

You've selected b.itam_first_name and b.itam_last_name twice in the select list. Did you mean to? Should the last two have itrm instead of itam?

SELECT A.itam_relevant_appl_code as ret,
 b.service_id,
 b.it_service,
 b.itam_user_id,
 b.itam_last_name, -- 1st occurrence
 b.itam_first_name,  -- 1st occurrence
 b.itrm_user_id,
 b.itam_first_name, -- 2nd occurrence
 b.itam_last_name -- 2nd occurrence
FROM ...

If this wasn't an error, you will have to give the 2nd occurrences of the column an alias to allow Oracle to differentiate between the two.

ETA: Looking at your query a bit more closely, you're only actually using 3 columns from the source query:

a.itam_relevant_appl_code
b.service_id
b.it_service

So you could just get rid of the other columns, as they aren't relevant to the merge.

Column ambiguously defined error with Oracle Merge statement

1 Answers