RE: SQL Question of the day

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

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.