How can you delete duplicate rows without using te

joefawcett · October 18th, 2007, 02:54 AM

Quote:

quote:Originally posted by Jeff Moden
Or, you could just design your table correctly to begin with... Primary keys work wonders ;)

--Jeff Moden

We don't need no stinkin' primary keys :)

--

Joe (Microsoft MVP - XML)

Peso · October 18th, 2007, 03:20 AM

Code:

DECLARE    @Sample TABLE (A INT, B INT, C INT)

INSERT    @Sample
SELECT    1, 1, 1 UNION ALL
SELECT    1, 1, 1 UNION ALL 
SELECT    1, 1, 1 UNION ALL
SELECT    1, 1, 1 UNION ALL
SELECT    2, 2, 2 UNION ALL
SELECT    2, 2, 2 UNION ALL
SELECT    2, 2, 2 UNION ALL
SELECT    2, 2, 3

WHILE 1 > 0
    BEGIN
        SET ROWCOUNT 1

        DELETE        f
        FROM        @Sample AS f
        INNER JOIN    (
                    SELECT TOP 1    A,
                            B,
                            C
                    FROM        @Sample
                    GROUP BY    A,
                            B,
                            C
                    HAVING        COUNT(*) > 1
                ) AS m ON m.A = f.A AND m.B = f.B AND m.C = f.C

        IF @@ROWCOUNT = 0
            BREAK
    END

SET ROWCOUNT 0

SELECT * FROM @Sample

Jeff Moden · October 18th, 2007, 09:24 PM

Peter has the right idea if you don't want to use a temp table...

--Jeff Moden

Jeff Moden · October 18th, 2007, 09:28 PM

Quote:

quote:LOL, true, but back to the real world it can still happen...

Quote:

i had it happen tonight when loading in data from an external CSV data source.
Although the data had unique rows in the CSV file, by the time i had ran my clean up script to clean up the data i was left with some duplicate rows - not all fields were duplicate but enough fields to cause me problems.

Heh... You load external uncleaned data directly into your production tables?

I always load external data into a staging table for cleaning... a little bit slower and a whole lot safer.

Also, I'm thinking that tables without primary keys are "death by SQL"... :D

--Jeff Moden

Jeff Moden · October 18th, 2007, 09:43 PM

OMG! Peter! Your code works great if there are only a handful of dupes... but when there's a lot (like if you overlayed two 500k row input files, for example), it takes about a million years because of the single row deletes and the SET ROWCOUNT 1! I think the OP is a bit insane for NOT wanting to use a temp table with a surrogate key.

--Jeff Moden

joeman · October 19th, 2007, 06:51 AM

Quote:

quote:Originally posted by Jeff Moden

Quote:

quote:LOL, true, but back to the real world it can still happen...

Quote:

i had it happen tonight when loading in data from an external CSV data source.
Although the data had unique rows in the CSV file, by the time i had ran my clean up script to clean up the data i was left with some duplicate rows - not all fields were duplicate but enough fields to cause me problems.

Heh... You load external uncleaned data directly into your production tables?

I always load external data into a staging table for cleaning... a little bit slower and a whole lot safer.

Also, I'm thinking that tables without primary keys are "death by SQL"... :D

--Jeff Moden

Of course i dont load it directly into production tables!! im crazy, but not that crazy!! :D

I have a totally separate staging database on a second machine so that i can mess about with the data as much as i want without using up valuable production resources. my prod server is strained enough as it is!

Once the data has been loaded from a mixture of CSV and XML files into the staging tables, i then need to clean it up and ensure there are no duplicate rows, and ensure that the fields that will eventually map to the production primary key fields are actually unique...
So you see i do use primary keys, just not in my staging tables

Joe
www.joesdirectory.co.uk