× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Yikes!

So you recommend the rrn as the key to record selection? Not a bad idea. I have run up against having to have multiple key fields in select/sub-selects and this simplifies things. Thanks.

Joe's solution worked in this particular case because there were no dups in FileA created by the update. However, I know that last week when I was running some of these I got hammered a few times and couldn't unravel the mystery of how/why the duplicates were occurring. And, yes, the update of the key field was the problem.

One other approach would be to run a delete in File A of the potential duplicates and then change the dates in File B and insert new records into File A. More than one way to skin the cat, although I think I feel more comfortable omitting the problem records rather than deleting them.

I plan to front end this with a prompt screen and then run this as embedded SQL with just a couple of additional record selections.

Thanks for the help.

Pete


Wilt, Charles wrote:
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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.