| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 103662 | 2009-10-02 00:48:00 | SQL Server delete duplicate entry | Gobe1 (6290) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 816013 | 2009-10-02 00:48:00 | 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.... |
Gobe1 (6290) | ||
| 816014 | 2009-10-02 00:58:00 | How many rows are duplicated? | somebody (208) | ||
| 816015 | 2009-10-02 01:32:00 | About 165, was enting a card list of magic the gathering set just playing around | Gobe1 (6290) | ||
| 816016 | 2009-10-02 01:51:00 | 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. |
nofam (9009) | ||
| 816017 | 2009-10-02 01:57:00 | 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 :) |
Gobe1 (6290) | ||
| 816018 | 2009-10-02 02:06:00 | 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) |
nofam (9009) | ||
| 816019 | 2009-10-02 02:08:00 | 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. | somebody (208) | ||
| 816020 | 2009-10-02 02:09:00 | 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 |
Gobe1 (6290) | ||
| 1 | |||||