Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

Delete duplicates

I have a table that has addresses
I need to delete the duplicates. I know there is a duplicate if the 'street' is exactly the same. I need to keep ONE of the rows that are duplicate, if there are no duplicates leave the record as is.

table = addresses
field = street

I tried this but did not work out:

DELETE FROM [Addresses] WHERE [Street] IN (
    SELECT id FROM [Addresses] GROUP BY id HAVING ( COUNT([Street]) > 1 )
)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Paul, i think you meant to partition by street order by id, and in the first part in finding them, s/b select * from CTE.
@Mark,
I don't think ID is useful in partitioning. What I was attempting to convey is that somewhere in the address table I would expect to see a foreign key, it might be to a person (e.g. a "user") or to a client/customer or even a supplier. This detail isn't provided in the question, so I used a invented column name "persionid" to represent that foreign key. Replace "personid" with the correct column name.

If such a foreign key does not exist in the table then I'm not sure what would be done to be honest.

@Aleks

Can you tel use what the column names are in the table? and if possible provide a few rows as samples?
@Paul, now understand 'personID' thought it was ID.... thanks for clarifying.
Avatar of Aleks

ASKER

I felt like this seemed too complex. What I did is export the data to another table with "Select Distinct" and then import it.
@Aleks, good call as a once off, but if it happened once....... Might want to accommodate in Queries