SQL Question of the day

Looks like SQL is become more popular given the traffic on the subject lately. I have a problem similar to Jon in that I have two tables to update. The relationship is that for each occurrence of a date in one file, I need to have a corresponding record in another file. The only wrinkle is that one file can have only one occurrence. The other file can have several.

For instance

File A keys: ssn, job, date

File B keys: ssn, job, line number (date is a non-key field in the record)

For each ssn,job and date in File B (which can have multiples) there is only one record in File A.

----------------------------------------------
| File A
----------------------------------------------
| ssn | job | date
----------------------------------------------
| 0023 | Cust | 070725
| 0023 | Fodserv| 070728
| 0023 | Fodserv| 070810

----------------------------------------------
| File B
----------------------------------------------
| ssn | job | date | Line #
----------------------------------------------
| 0023 | Cust | 070725 | 0001
| 0023 | Cust | 070725 | 0002
| 0023 | Cust | 070725 | 0003
| 0023 | Fodser| 070728 | 0001
| 0023 | Fodser| 070728 | 0002
| 0023 | Fodser| 070728 | 0003
| 0023 | Fodser| 070810 | 0001

In this case I need to re date all the records in FileB to 070810 and then make sure that there is one corresponding record with that date in FileA. (Later, using a delete SQL statement I can remove the record in FileA that has been "orphaned" by the re dating in File B)

So I used these two SQL statements:

update FileB b set b.date = 070810

update FileA a set a.date = 070810
where exists
(select b.job,b.ssn from ppay408b b where a.job=b.job and
a.ssn = b.ssn and a.date <> 070810)

I ended up with an error about a duplicate record in FileA (presumably because the FileA record with | 0023 | Fodserv| 070728 would be updated to | 0023 | Fodserv| 070810 which already exists. So the question is: How do I omit records that could potentially become duplicates? In the case above, the SQL would attempt to update the first two records since they did not have a date value of 070810. The follow up question is: Is there a way to update both files at once?

Thanks,

Pete Helgren