PDA

View Full Version : SQL Server delete duplicate entry



Gobe1
02-10-2009, 01:48 PM
Hi, i am trying to delete a duplicate entry in a test database and have searched google but the methods are very long winded but it works
I accidentaly inserted the same information twice as the first time it had an error so i inserted it again then the error was this data exists...:angry
Is there an easier way??

Any help will be thankfully accepted as i am bound to do this again....

somebody
02-10-2009, 01:58 PM
How many rows are duplicated?

Gobe1
02-10-2009, 02:32 PM
About 165, was enting a card list of magic the gathering set just playing around

nofam
02-10-2009, 02:51 PM
Something like this:


SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1


You can 'cheat' in a simple small database by doing a query SELECT * from your tables, and applying a Group By to the dataset; provided the records are exactly the same across all fields, this will effectively remove dupes. You can then just drop your table and re-create it, or delete the contents and insert the dataset into it.

Gobe1
02-10-2009, 02:57 PM
The data was exactly the same but was only inserted twice with about 10 seconds in between inserts, would this method remove them or is there other data i couldnt see like timestamp ???
I can reproduce it very easily and give it a shot

Otherwise thanks :)

nofam
02-10-2009, 03:06 PM
Not sure about timestamp to be honest, but I would've thought that was metadata rather than something you would see in a field?

Just do the query first with the Group By and see if that removes them (queries are non-destructive remember)

somebody
02-10-2009, 03:08 PM
If you have a primary key field that is sequential, you could simply find the begin and end rows of your 2nd attempt at inserting and do a DELETE FROM tablename WHERE columnName BETWEEN xxx AND yyy.

Gobe1
02-10-2009, 03:09 PM
Awesome thanks i will give it a go next week as im about to go home (Friday night yeah)
I will let you know if it doesnt work (aint that always the way)
Cheers for the help Somebody and Nofam