conditional statement for a nomber of records

[munitjsr2]

have got this update script updating certain columns

update oppar
set oppar_run_mode = 0,
    oppar_run_time = 0, 
    oppar_interval_ind = 'N' , 
    oppar_destination = '',
    oppar_run_date ='', 
    oppar_run_interval=''
    where ( oppar_job_name, oppar_job_rec ) 
    in 
     ( ('CSCLM' , 'XYZ')
   , ('ARCLEVEXT' , 'LMN'));

But there are cases where there is no record in the table oppar where the column oppar_job_rec is XYZ or LMN.

Now I need to verify the existence of oppar_job_name=CSCLM then if that exists i need to check the existence of the Job rec coresponding to CSCLM i.e oppar_job_rec=XYZ and if it does not exists i need to add a new record with these details.

oppar_job_name=CSCLM
oppar_job_rec=XYZ
oppar_run_mode = 0
oppar_run_time = 0
oppar_interval_ind = 'N'
oppar_destination = ''
oppar_run_date =''
oppar_run_interval=''

if it exists then i need to update that row.

please help and tell me if you need more info.

but how do i perform the checking if it could be done and i need to do this on about 100 records with different values for oppar_job_rec .

Orcale9i Enterprise editon release 9.2.8.0 - 64 bit Production

[seslie]

Hi munitjsr2,

So, let me see if i get you right.

You have a table: Oppar that needs to be updated based on the script..

But there are cases where there is no record in the table oppar where the column oppar_job_rec is XYZ or LMN.

You would need a particular column to identify an 'empty' record. I assume every other column in the table will be null except for the job_name, job_rec and probably the primary key.

Now I need to verify the existence of oppar_job_name=CSCLM then if that exists i need to check the existence of the Job rec coresponding to CSCLM i.e oppar_job_rec=XYZ and if it does not exists i need to add a new record with these details.

DECLARE
  cnt NUMBER;
BEGIN
  SELECT COUNT (*)
  INTO cnt
  FROM oppar
  WHERE oppar_job_name = 'CSCLM' and oppar_job_rec = 'XYZ'
  or oppar_job_name = 'ARCLEVEXT' and oppar_job_rec = 'LMN'
  AND [I]particular_column[/I]= NULL

 IF (cnt > 0)
 THEN

   //UPDATE TABLE

 ELSE

  //INSERT INTO TABLE 

ENDIF;
END;