What is the fastest way to delete duplicate rows from a table? 
Author Message
 What is the fastest way to delete duplicate rows from a table?
Online 7.x

 What is the fastest way to delete duplicate rows from a table?

 I don't really want anything that relies on rowids, because the table
 may eventually be fragmented.

 Thanks :)

Sent via Deja.com http://www.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?

First off, one should always maintain a unique value within a table.  The
reliance on rowid is overused since, as you mention, rowid within a
fragmented table is not unique.

You can, of course, use the syntax WITH ROWID when you fragment the table to
give it an unique value once again.

Take care.
Clifton Bean


Quote:
> Online 7.x

>  What is the fastest way to delete duplicate rows from a table?

>  I don't really want anything that relies on rowids, because the table
>  may eventually be fragmented.

>  Thanks :)

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?
For fragmented  table use

BEGIN WORK;
SELECT DISTINCT * FROM Table INTO TEMP Temp1;
DELETE FROM Table WHERE 1 = 1;
INSERT INTO Table SELECT * FROM Temp1;
COMMIT WORK;

Other Way can be
create schema for the table using dbschema -d database -t table -ss
create.sql on unix prompt
Then run
SELECT DISTINCT * FROM Table INTO TEMP Temp1;
Drop table  Table
Recreate table using create.sql
INSERT INTO Table SELECT * FROM Temp1;

Sameer Tumbde



Quote:
> First off, one should always maintain a unique value within a table.  The
> reliance on rowid is overused since, as you mention, rowid within a
> fragmented table is not unique.

> You can, of course, use the syntax WITH ROWID when you fragment the table
to
> give it an unique value once again.

> Take care.
> Clifton Bean



> > Online 7.x

> >  What is the fastest way to delete duplicate rows from a table?

> >  I don't really want anything that relies on rowids, because the table
> >  may eventually be fragmented.

> >  Thanks :)

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?

Quote:

> Online 7.x

>  What is the fastest way to delete duplicate rows from a table?

>  I don't really want anything that relies on rowids, because the table
>  may eventually be fragmented.

First if your fragmented table is not created WITH ROWIDS there is NO way
to easily remove duplicates.  All schemes for removing duplicate rows from
a table that does not have a unique key depend on the rowid to BE that
unique key.  It is cleanups like this and the garbaging up that causes one
to have to perform them that prompt Relational hardliners (myself included)
to insist that EVERY table have a unique key for each row.  There is NO GOOD
reason for not having a unique key on any table!

Now WITH rowids you can:

select {keycols}, count(*)
from atable
group by {keycols}
having count(*) > 1
into temp fred;

delete from atable
where rowid in (
        select min(t.rowid)
        from atable t, fred f
        where {join conditions on {keycols} between t & f}
        group by {keycols}
);

If you do not have rowids the ONLY solution is to SELECT DISTINCT into a
new table, drop the old one, and rename the new one.  HOWEVER, if there
are columns other than the keys that might differ and you want to drop all
but one of those anyway it gets more complicated yet.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?

Quote:
> select {keycols}, count(*)
> from atable
> group by {keycols}
> having count(*) > 1
> into temp fred;

> delete from atable
> where rowid in (
>         select min(t.rowid)
>         from atable t, fred f
>         where {join conditions on {keycols} between t & f}
>         group by {keycols}
> );
> Art S. Kagel

That's funny, Art: every time you need a temp table (not worth to stay after
use)
you call it "Fred". Does that mean you had a guy in your childhood named Fred
you didn't like at all ?

It would be funny to know every DBAs favourite "garbage-name"...    :-)

By the way: my garbage table sounds most of the time "jau" or "jepp" or
"jawoll"...



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?
Thanks for your input guys, much appreciated :)

Regarding naming of temp tables, maybe the optimiser looks favorably on
temp tables called Fred? :)


Quote:

> Online 7.x

>  What is the fastest way to delete duplicate rows from a table?

>  I don't really want anything that relies on rowids, because the table
>  may eventually be fragmented.

>  Thanks :)

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?


Quote:


>> >         from atable t, fred f

>>That's funny, Art: every time you need a temp table (not worth to stay
>>after
>>use)
>>you call it "Fred". Does that mean you had a guy in your childhood named
>>Fred
>>you didn't like at all ?

>>It would be funny to know every DBAs favourite "garbage-name"...    :-)

>>By the way: my garbage table sounds most of the time "jau" or "jepp" or
>>"jawoll"...

>German people are completely normal. It's the rest of us that are weird,

I would have thought he'd name a temp table 'kaput'.

Quote:
>obviously. Well, in my case there was a guy called "t1" I really hated...

I had a boss that always used fred for his temp tables and files because the folks that taught him used fred. I started using 'booger' just to be contrary. Other folks would look at him strangely when he'd say, "Clean up all your boogers, we're running out of space." when I'd have a few really large temp files on a smallish file system.

==

carlos

Maintainer of the procrastinator's FAQ
Well, maybe tomorrow I will be.

_____________________________________________________________
Want a new web-based email account ? ---> http://www.firstlinux.net



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?

Quote:

> > select {keycols}, count(*)
> > from atable
> > group by {keycols}
> > having count(*) > 1
> > into temp fred;

> > delete from atable
> > where rowid in (
> >         select min(t.rowid)
> >         from atable t, fred f
> >         where {join conditions on {keycols} between t & f}
> >         group by {keycols}
> > );
> > Art S. Kagel

> That's funny, Art: every time you need a temp table (not worth to stay after
> use)
> you call it "Fred". Does that mean you had a guy in your childhood named Fred
> you didn't like at all ?

> It would be funny to know every DBAs favourite "garbage-name"...    :-)

> By the way: my garbage table sounds most of the time "jau" or "jepp" or
> "jawoll"...

Actually I tend to name temp tables after Flintstones characters:
Fred, Barney, Wilma, Betty, Pebbles, ...  

I have not had a need to go to Bam Bam yet though ;-)

Lord knows why I started that, and please don't tell my shrink.

Art S. Kagel



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?

Quote:

> > select {keycols}, count(*)
> > from atable
> > group by {keycols}
> > having count(*) > 1
> > into temp fred;

> > delete from atable
> > where rowid in (
> >         select min(t.rowid)
> >         from atable t, fred f
> >         where {join conditions on {keycols} between t & f}
> >         group by {keycols}
> > );
> > Art S. Kagel

> That's funny, Art: every time you need a temp table (not worth to stay after
> use)
> you call it "Fred". Does that mean you had a guy in your childhood named Fred
> you didn't like at all ?

It's just a table called fred. It's just YOU who sees a guy from your
childhood.... Would you like to talk about that?

.... Because Art's shrink would be happy to listen! :-)

Quote:
> It would be funny to know every DBAs favourite "garbage-name"...    :-)

Now there's another negative term. To me, every file I create has a use,
otherwise I wouldn't have created it. Therefore, none of my temporary
files are garbage, ;-)

Quote:
> By the way: my garbage table sounds most of the time "jau" or "jepp" or
> "jawoll"...

... Now this guy called Jau, or Jepp, or Jawoll who you knew from your
childhood, and you think of as 'garbage'......,

... did he used to beat you up?.....   :-))

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

| http://www.informix.com  http://www.informixhandbook.com |/////  / //|
| http://www.iiug.org  +-----------------------------------+////  / ///|
|                      |Dr. Stock is listening....  So his |///  / ////|
|                      |eyes are closed & he's snoring, but|//  / /////|
|                      |that doesn't mean he doesn't care. |/  ////////|
+----------------------+-----------------------------------+-----------+



Wed, 18 Jun 1902 08:00:00 GMT
 What is the fastest way to delete duplicate rows from a table?


Quote:

>Actually I tend to name temp tables after Flintstones characters:
>Fred, Barney, Wilma, Betty, Pebbles, ...  

>I have not had a need to go to Bam Bam yet though ;-)

>Lord knows why I started that, and please don't tell my shrink.

It's probably that chewable vitamin {*filter*}ion of yours. Oops. Was I not supposed to tell?

carlos

==
Maintainer of the procrastinator's FAQ.                                                                                                                                                                                                                                                                                                                                                                   Well, maybe tomorrow I will be.

_____________________________________________________________
Want a new web-based email account ? ---> http://www.***.com/



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. The Fastest Way To Delete All Rows In A Table

2. how to delete duplicate rows in table

3. Delete duplicate rows from a table

4. how to delete the duplicated rows from a table

5. Another Duplicate Rows, Delete All But One Row

6. Deleting rows from one table and adding those rows to another table at the same time

7. fastest isql scripts to find duplicate rows

8. I am getting duplicate records, I mean everything is duplicate even Access record number

9. Delete duplicate row?

10. Delete duplicate rows question.

11. Delete the duplicate row ?


 
Powered by phpBB® Forum Software