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:
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 )
)
Microsoft SQL ServerSQL
Last Comment
Mark Wills
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@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?
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.
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