RE: SQL Question of the day

Pete,

The key idea to think about here is, how to I know which record to update?

Normally, the file would have a primary key which identifies a particular record uniquely. In this
case, you're trying to change a field that is part of the primary key of file A.

So the question: How do you decide which record to update if you were updating the records manually?

Then: how do I get SQL to make the same decision?

One answer, which ever one I come to first.

Something like this would take care of that:
update ctsc_w/test B
set date = '2007-08-11'
where rrn(b) in (select key
from ( select min(rrn(a)) as key, ssn, job
from ctsc_w/test A
group by ssn, job
) as tbl
)

Of course, that leaves one problem. If there's already a record with the date you want to update to.
So we need to add some more:

update ctsc_w/test B
set date = '2007-08-10'
where rrn(b) in (select key
from (select min(rrn(a)) as key, ssn, job
from ctsc_w/test A
group by ssn, job
) as tbl
where not exists (select *
from ctsc_w/test C
where tbl.ssn = c.ssn
and tbl.job = c.job
and c.date = '2007-08-10'
)
)

HTH,

Charles

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







--
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.