I have a table named helthins. The table contains a field for user_comments. The table contains information about employees who recently switched health insurance carriers. Some employees will have two records in the table, one indicating their current carrier, the other indicating their previous carrier. The user_comments for these records are automatically generated based on a lookup table and the following to SQL statements:

UPDATE helthins
SET user_comment =
(SELECT suffix FROM helth_suffix_codes
WHERE dedtype IN (SELECT dedtype
FROM helth_currents
WHERE ssno = helthins.ssno ))
WHERE sortkey = 'P'

UPDATE helthins
SET user_comment =
(SELECT suffix FROM helth_suffix_codes
WHERE dedtype IN (SELECT dedtype
FROM helth_priors
WHERE ssno = helthins.ssno ))
WHERE sortkey = 'C'

Before these statements can be run, the temporary tables that the statements use (helth_priors and helth_currents) are created by inserting into the tables from the arrays created in these statements:

SELECT ssno, dedtype, sortkey;
FROM helthins_view;
WHERE sortkey = 'C' ;
INTO ARRAY aHelth_Currents

SELECT ssno, dedtype, sortkey;
FROM helthins_view;
WHERE sortkey = 'P';
INTO ARRAY aHelth_Priors

Now my problem (finally!): I've been told I could eliminate the need for the temporary tables by modifying the first two SQL statements to incorporate this information, but I have no idea how! If I were to put this into simpler(?) terms, this is what I want to do:

Update all records in helthins that have the same SSN number, and have in one record a sortkey of "P" and the other record a sortkey of "C"; update the "C" record with the suffix from the lookup table (helth_suffix_codes) where the dedtype = the dedtype of the record in helthins having the same SSN number and a sortkey of "P". I also need to update the "P" records with the suffix that corresponds to the dedtype of the "C" record.

Can anybody help?

Jeff