|
midrange.com
Ah, good point.
I don't think you can.
You could use a Global temporary table instead.
DECLARE GLOBAL TEMPORARY TABLE 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
);
Notice, that you have two separate statements now.
If you want a single statement, you could try...
DELETE FROM SSW85 A
Where not exists( select *
from ( SELECT
SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) AS FDATE
FROM SSW85
GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP,
ORDERNUM) 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
)
But I've got doubts on performance....
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 DeLong, Eric
> Sent: Tuesday, May 16, 2006 3:45 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: 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.
> >
> >
>
> --
> 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.
>
>
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
