RE: SQL Question of the day

From: Pete Helgren

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 straightforward way is to do pretty much do exactly what you said, noly
express it in SQL:

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

The second clause sees if any records with the target date exist in FileA
before attempting the update. Note I did the "select 1" technique rather
than "select b.job,b.ssn" because in an existence check you don't care about
columns, just whether a row exists or not.

Disclaimer: I did NOT test this statement! But it seems pretty reasonable.

The follow up question is: Is there a way to update both files at once?

I can't imagine a way to do it except maybe with commitment control. If
you're worried about an atomic update (that is, being sure nothing sneaks in
between your two SQL statements), then an easy answer would be to issue an
ALCOBJ *EXCL on both files before starting the SQL statements.

Joe