oracle merge statement

Question

The following statement is in a Stored Procedure MERGE INTO table1 a

USING (SELECT a.ROWID row_id, RTRIM(NVL(c.address_line1_text, '')) || ' ' || 
RTRIM(NVL(b.unit_number, '')) address_line1_text, 
RTRIM(c.city_name) city_name, RTRIM(c.state_code) state_code, 
RTRIM(c.basic_zip_code) basic_zip_code, 
NVL(b.unit_number, ' ') unit_number

FROM table1 a ,table2 b ,table3 c

WHERE 
     a.program_type_cd = 'P'

        AND a.development_code = b.development_number
    ) src

      ON ( a.ROWID = src.row_id )
      --(ERROR IN LINE BELOW)
      WHEN MATCHED THEN UPDATE SET street = RTRIM(NVL(src.address_line1_text, '')) 
      || ' ' || RTRIM(NVL(b.unit_number, '')),

                                   city = RTRIM(src.city_name),

                                   STATE = RTRIM(src.state_code),

                                   zip = RTRIM(src.basic_zip_code),

                                   unit_number = NVL(src.unit_number, ' ');

I am getting an error saying :

"B"."UNIT_NUMBER": INVALID IDENTIFIER

Answer 1

I think you should replace b.unit_number by src.unit_number here:

  || ' ' || RTRIM(NVL(src.unit_number, '')), /* <-- */

                               city = RTRIM(src.city_name),

                               STATE = RTRIM(src.state_code),

                               zip = RTRIM(src.basic_zip_code),

                               unit_number = NVL(src.unit_number, ' ');