RE: SQL Ideas Please

Can you use common table expressions with the DELETE statement?  I have just
come out of V5R1, and haven't played with V5R3 much yet....

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Wilt, Charles
Sent: Tuesday, May 16, 2006 2:36 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Ideas Please


Can you use a join-table clause in a delete statement?

I didn't think so, but maybe.

If not try:
WITH preserve AS (
  SELECT SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) AS FDATE 
  FROM SSW85
  GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM
)
DELETE FROM SSW85 A
Where not exists( select * 
                  from preserve P
                  where A.SCACODE  = P.SCACODE  AND 
                        A.TRLRNUM  = P.TRLRNUM  AND 
                        A.ASNNUM   = P.ASNNUM   AND
                        A.ORDERTYP = P.ORDERTYP AND
                        A.ORDERNUM = P.ORDERNUM AND 
                        A.FDATE   = P.FDATE
                 )


The same format would work for the first request to:
WITH preserve AS (
  SELECT distinct SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM 
  FROM SSW85
)
DELETE FROM SSW85 A
Where not exists( select * 
                  from preserve P
                  where A.SCACODE  = P.SCACODE  AND 
                        A.TRLRNUM  = P.TRLRNUM  AND 
                        A.ASNNUM   = P.ASNNUM   AND
                        A.ORDERTYP = P.ORDERTYP AND
                        A.ORDERNUM = P.ORDERNUM
                )

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
> Sent: Tuesday, May 16, 2006 3:23 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL Ideas Please
> 
> Brian, I'm not sure I fully understood you (especially 
> DISTINCT part - SQL
> keyword?) so make sure you test this on a dummy/copied file first.  
> Alternatively, just run the SELECT part on its own to verify 
> that projected
> rows are ones you want to preserve.
> 
> WITH preserve AS (
>   SELECT SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) 
> AS FDATE 
>   FROM SSW85
>   GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM)
> DELETE FROM SSW85 A, preserve P
> WHERE A.SCACODE  = P.SCACODE  AND 
>       A.TRLRNUM  = P.TRLRNUM  AND 
>       A.ASNNUM   = P.ASNNUM   AND
>       A.ORDERTYP = P.ORDERTYP AND
>       A.ORDERNUM = P.ORDERNUM AND 
>       A.FDATE   <> P.FDATE
> 
> Elvis
> 
> -----Original Message-----
> Subject: SQL Ideas Please
> 
> Hi All,
> 
> (This is a two-part SQL question)
> 
> Can someone tell me if there is an easy way to delete records from a
> table where there are only four data elements on which I wish to base
> the delete routine?  For example, I have a table that has numerous
> records in it.  I want to delete all records other than those 
> that have
> a distinct SCACCODE, TRLRNUM, ASNNUM, ORDERTYP and ORDERNUM.  However,
> I'm not sure how to express this in SQL, but in pseudo-code, 
> I'd like to
> do this:
> 
> DELETE ALL RECORDS IN SSW85 EXECPT THOSE RECORDS THAT HAVE A DISTINCT
> (SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM).
> 
> My second SQL statement needs to pass over the records again and only
> keep records that have the minimum number for a specific column.  For
> example, if I have these records:
> 
> A          B          C                      D          E          F
> 
> ABCD   1000     10039210          2          105049  206052971
> ABCD   1000     10039210          2          105049  206052951
> ABCD   1000     10039210          2          105049  206052991
> ABCD   1000     10039210          2          105049  206053001
> 
> I only want to keep one of these records that has the minimum value in
> column F.  So if I ran a min(COLUMN_F) on this, I would only get back
> the second record (since it is the lowest number for Column F).
> Pseudo-code wise, I'd need something like:
> 
> DELETE ALL RECORDS IN SSW85 EXCEPT EACH DISTINCT (SCACCODE, TRLRNUM,
> ASNNUM, ORDERTYP, ORDERNUM) WITH THE LOWEST KDLOTSTR.
> 
> Is there an easy way to do both of these statements (or combine them
> into one statement)?
> 
> Thanks!
> 
> Brian. 
> 
> 
> -- 
> 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.
> 
>