I have the following external table:

CREATE TABLE my_ext
(c1 VARCHAR2(3),
c2 VARCHAR2(2),
c3 VARCHAR2(25),
c4 VARCHAR2(10),
c5 VARCHAR2(3)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_dat_dir   
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
BADFILE my_log_dir:'my_ext%a_%p.bad'
LOGFILE my_log_dir:'my_ext%a_%p.log'
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
( c1, c2, c3, c4, c5
)
)
LOCATION ('mydatafile.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;
ALTER SESSION ENABLE PARALLEL DML;

Data in mydatafile.dat looks like this and is what needs to be loaded into the table below:
020|MD|Fan Belt|456-CK|9C3
079|MD|Head Lamp|9JKL-3450|SS8
079|ND|Head Lamp|9JKL-3450|3HL
033|ND|Exhaust Fan|SSLEXH-73|SMS

I have the following database table:

CREATE TABLE PARTS
(
  P_ID   CHAR(3 BYTE)    NOT NULL,
  DEPT   CHAR(2 BYTE),
  DESC   CHAR(25 BYTE),
  PARTNO   CHAR(10 BYTE),
  ORGNO   CHAR(3 BYTE),
)

With the following data in it:
insert into parts values ('020','MD','Fan Belt','456-CK2','9C3',);
insert into parts values ('079','ND','Head Lamp Assy','9JKL-3450','3HL',);

I need to upsert data from the my_ext external table to the parts db table.

I need to insert records that exist in my_ext but do not exist in the PARTS db table.
If record exists in both my_ext and parts db table (c1 = p_id ) I must then evaluate to see, have any column values changed (for example some column value(s) in the my_ext external table does not match column value(s) in the parts db table), if yes, I must update the record in the PARTS db table.

I need to upsert all records where my_ext.c2 = 'ND' (in the example data, it must insert/update the 3rd record where c1 has the value 079 and c2='ND' as well as the record where c1 has the value 033 and c2='ND')

and also, if the only record that exists in my_ext is a part record that has a c2 value of 'MD' (meaning no 'ND' record is available for that part) - then I need to upsert using the 'MD' record (for example, I must insert the example data where c1 = '020' and c2 = 'MD' b/c there is no record where c1 = '020' and c2 = 'ND')

The datafile contains 3728424 records. The database table in which the records will be loaded has 4,945,616 records.

What is the best way to do this? Can you provide example code or a sql or plsql keyword that I can use to search on to learn how to do this.
Thank you.

I will need to evaluate both 'MD' and 'ND' records. The 'ND' record takes precedence over a 'MD' record.
If the only record in my_ext, is a 'MD' record, we must load it (as is the case w/ example record 020 above).
If both a 'MD' and a 'ND' record exist in my_ext (as is the case w/ example record 079 above), we must choose to load the 'ND' record.
The are two allowable values for c2: 'ND' or 'MD'.
c1 + c2 identifies one unique record in my_ext.
There will never be more than two records in my_ext with the same c1 value.
If two records exists with the same c1 value,
upsert the record where c2 = 'ND'
else (only one record exists)
upsert the one record (it might be a c2 = 'MD' or a c2 = 'ND').

The example data is data about parts (things we install into various crafts). No significance/exceptional processing. We could remove the word "part" from my description and replace it w/ the word "record" to avoid confusion.

ops$tkyte%ORA10GR2> merge into parts
  2  using (
  3  select p_id, dept, des, partno, orgno
  4    from (
  5  select c1 p_id, c2 dept, c3 des, c4 partno, c5 orgno,
  6         row_number() over (partition by c1 order by decode(c2,'ND', 1, 'MD', 2) ) rn
  7    from my_ext
  8         )
  9   where rn = 1
 10   MINUS
 11  select *
 12    from parts
 13         ) new_data
 14  on (parts.p_id = new_data.p_id)
 15  when matched then update set dept = new_data.dept, des = new_data.des, partno = new_data.partno, orgno = new_data.orgno
 16  when not matched then insert values ( new_data.p_id, new_data.dept, new_data.des, new_data.partno, new_data.orgno )
 17  /

3 rows merged.