Merge and Invalid Identifier Error.

Today early in the morning my boss called me and gave me a requirement to load a file into a table. And this new table has to be merged with the existing table in the database. i.e if it has any new rows then insert into the existing table and for the existing rows if there are new additions then update the existing table. I was so happy that i knew the oracle's merge command and thought I would finish my work in no time. My happiness lasted only till i encountered the "ORA-00904: invalid identifier" error.

Well here is what happened.

SQL> desc ms_zip_code_dtl_main;
Name Null? Type
----------------------------------------- -------- ------------------

COUNTRY_CD NOT NULL CHAR(2)
STATE_ID NOT NULL CHAR(2)
ZIP NOT NULL VARCHAR2(30)
CITY NOT NULL VARCHAR2(35)
COUNTY_NM VARCHAR2(50)
ENTRY_DT NOT NULL DATE
ENTRY_USR NOT NULL CHAR(8)

SQL> drop table ms_zip_code_dtl_main_temp;

Table dropped.

Elapsed: 00:00:01.09
SQL> CREATE TABLE ms_zip_code_dtl_main_temp AS
2 SELECT *
3 FROM ms_zip_code_dtl_main;

Table created.

SQL> merge
2 into ms_zip_code_dtl_main a
3 using ms_zip_code_dtl_main_temp b
4 on(a.zip = b.zip
5 and a.country_cd = b.country_cd
6 and a.state_id = b.state_id
7 and a.city = b.city)
8 when matched then
9 update
10 set a.country_cd = b.country_cd,
11 a.state_id = b.state_id,
12 a.city = b.city,
13 a.county_nm = b.county_nm,
14 a.entry_dt = b.entry_dt,
15 a.entry_usr = b.entry_usr
16 when not matched then
17 insert(a.country_cd,
18 a.state_id,
19 a.zip,
20 a.city,
21 a.county_nm,
22 a.entry_dt,
23 a.entry_usr)
24 values(b.country_cd,
25 b.state_id,
26 b.zip,
27 b.city,
28 b.county_nm,
29 b.entry_dt,
30 b.entry_usr)
31 /
and a.country_cd = b.country_cd
*
ERROR at line 5:
ORA-00904: "A"."COUNTRY_CD": invalid identifier

And then i saw this invalid identifier error and then i was thinking why the hell is it giving me this error. Because country_cd is very well a column in both the table. And then after some juggling i went on the all answers site Google and found this link which opened my eyes.

The problem in my case is that i am updating the columns that i am using in the join between the two tables. Actually if you think it really doesn't make any sense updating the columns when you got the rows on the condition that both the columns are equal.

What next, i made the change and my code works just fine.
SQL> ed
Wrote file afiedt.buf

1 merge
2 into ms_zip_code_dtl_main a
3 using ms_zip_code_dtl_main_temp b
4 on(a.zip = b.zip
5 and a.country_cd = b.country_cd
6 and a.state_id = b.state_id
7 and a.city = b.city)
8 when matched then
9 update
10 set a.county_nm = b.county_nm,
11 a.entry_dt = b.entry_dt,
12 a.entry_usr = b.entry_usr
13 when not matched then
14 insert(a.country_cd,
15 a.state_id,
16 a.zip,
17 a.city,
18 a.county_nm,
19 a.entry_dt,
20 a.entry_usr)
21 values(b.country_cd,
22 b.state_id,
23 b.zip,
24 b.city,
25 b.county_nm,
26 b.entry_dt,
27* b.entry_usr)

And time and again it thought me not to assume things.