RE: Using SQL to delete "duplicate" record

[Alan Shore <AlanShore@xxxxxxxx>]

AAAAAAAAAAHHHHH did not see that
No wait - I mean
At last - someone spotted my deliberate mistake


Tom
All kidding aside - thanks, that was it
Could NOT see the wood for the trees

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


                                                                       
             Tom E Stieger                                             
             <testieger@finewi                                         
             re.com>                                                    To
             Sent by:                  "'Midrange Systems Technical    
             midrange-l-bounce         Discussion'"                    
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
                                                                        cc
                                                                       
             11/04/2010 11:27                                      Subject
             AM                        RE: Using SQL to delete "duplicate"
                                       record                          
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




Alan-

I don't think you can have more than one *column* in your subselect.  I
think it is your b.* that is causing the problems.

-Tom Stieger
Engineer
California Fine Wire

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, November 04, 2010 6:23 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Using SQL to delete "duplicate" record


Morning all
I have a file that is keyed, but I have multiple records with the same key
that I need to delete but keep the latest record Fortunately there is a
create "date" on the file (really a 6 digit numeric field in mmddyy format)

here is my sql statement

Delete From FILE1 A
    Where RRN(A) in
(Select RRN(B),
b.* From FILE1b, FILE1 C
Where B.ord# = C.ord#  and
substr(digits(b.crtdat), 5, 2) || substr(digits(b.crtdat), 1, 4) < substr
(digits(c.crtdat), 5, 2) || substr(digits(c.crtdat), 1, 4))


Unfortunately I am receiving the following message Subselect with more than
one result column not valid.

I checked and confirmed that for the same Ord#, there are more than 2
records (5 or 6 in some cases)

Ord# is the only field that is the key. The other fields on the record have
no bearing between the duplicate ord# (Hopefully that last sentence makes
sense)

Anyone have any ideas how I can circumvent this problem Alan Shore
Programmer/Analyst, Direct Response E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
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 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.