Re: Deleting duplicates from table

I just remembered something that can do what you want - but be __very__ careful!

1. set up a SELECT statement that gives you the end result you want
2. Put that statement into a QMQRY - you can use STRQM, take option 1 to work with QM queries, be sure to set creation mode to SQL
3. Create a new QM query, to contain your SELECT
4. Make a backup copy of the file whose content you will replace
5. Put 9 in option and run the QMQRY
6. When prompted, for the output, select option 3, outfile
7. Put the name of the file there - the content will be replaced with the results of the SELECT statement

This is very risky, there is no indication of or protection from what you are doing - that is why you should back up the file first.

Regards
Vern

On 11/6/2024 9:49 AM, Vinay Gavankar wrote:

I think this should take care of it:

delete from myfile a
where a.fldd || a.flde || rrn(a) <
(select max( b.fldd || b.flde || rrn(b) ) from myfile b
where a.flda = b.flda and a.fldb = b.fldb and a.fldc = b.fldc)

Vinay

On Wed, Nov 6, 2024 at 10:43 AM Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:

If there are multiple records with the same flda thru flde values, they
will not be deleted.

I can live with that since I do not expect fldd and flde to be the same.

Of course if there any suggestions to overcome that, please let me know.

Vinay

On Wed, Nov 6, 2024 at 10:40 AM Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:

I want to keep the instance where the fldd and flde are highest.

This seems to work (fldd and flde are varchar fields):

delete from myfile a
where a.fldd || a.flde <
(select max( b.fldd || b.flde ) from myfile b
where a.flda = b.flda and a.fldb = b.fldb and a.fldc = bfldc)



On Wed, Nov 6, 2024 at 10:36 AM Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> wrote:

Do you want to keep only one instance of the dupes? Or do you want to
sweep them all out?

JS


El mié, 6 nov 2024 a las 9:20, Vinay Gavankar (<vinaygav@xxxxxxxxx>)
escribió:

I tried the below :
delete from myfile a
where rrn(a) >
(select min(rrn(b)) from myfile b
where a.flda = b.flda and a.fldb = b.fldb and a.fldc = b.fldc)

It kind of works, but I have no control over which records are

deleted. I

need to keep the record with highest value of fldd and flde (which are
receipt date and time).

Any suggestions?
Vinay



On Wed, Nov 6, 2024 at 9:01 AM Vern Hamberg via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Allow me to expand on Darren's post - when he says "...an example

from

DB2..." I believe he means Db2 for Linux/Unix/Windows - Vinay, the

link

that you give has 11.5 in it - that is the version for the LUW

version

of Db2 - it is not for IBM i.

The different versions or flavors of Db2 are very similar - IBM has a
website comparing them. Still, there are differences - here is a page
with links to resources -

https://www.ibm.com/support/pages/sql-reference-cross-platform-development-version-7

Regards
Vern

On 11/6/2024 7:21 AM, Darren Strong wrote:

I don't think you can delete from a virtual table like that.

You're

looking at an example from DB2, but are probably running this on an

IBM

I,

so its DB2 for I, which is different and maddeningly difficult to

Google.

Thanks IBM.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On

Behalf Of

Vinay Gavankar

Sent: Tuesday, November 5, 2024 10:33 AM
To: Midrange Systems Technical Discussion

<midrange-l@lists.midrangecom

Subject: Deleting duplicates from table

CAUTION: This email originated from outside of the organization.

Do not

click links or open attachments unless you recognize the sender and

know

the content is safe.

Hi,

I am trying to delete duplicate from a table, using following sql

(based

on example from
https://www.ibm.com/docs/en/db2/11.5?topic=statements-delete )

delete from
(select rownumber() over (partition by flda, fldb, fldc
order by fldd desc,

flde

desc) from myfile) as a(rn)

where rn > 1

I get an error over the first '(' before select:
Token ( was not valid. Valid tokens: <IDENTIFIER>.

What am I doing wrong?

TIA
Vinay
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To

subscribe,

unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at

https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.