RE: SQL Question of the day -- MIDRANGE-L

I'm not sure Joe's answer would work given the following data:

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

As it would attemp to update both "Cust" records and you'd still get the duplicate key.

On the other hand....mine would still work ;-)

Joe's is easier, so if it works with your data...go for it.

HTH,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Tuesday, July 31, 2007 10:20 AM
To: 'Midrange Systems Technical Discussion'
Subject: 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

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.