Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sdiwi
Starting Member
27 Posts |
 Posted - 2005-09-08 : 08:05:23
|
| hi.at the moment i'm restructuring some tables. the guy who created them did not use any primary keys. because there are some dups in the table, i cannot create new ones.is there a possibility to delete only one row?delete from table where criteria = x and criteria2 = y will delete all rowsdelete top 1 from table where criteria = x and criteria2 = y does not work (in mysql you can simply add limit 1 - i tried this as a pendant)i could write a sp to do this job (save the values - delete the rows and recreate them) - but i think there must be an easier solution...but it wouldn't be the first time, sql server makes the things more complicated than necessary.thanks for your help,peace,sdiwi. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2005-09-08 : 13:17:01
|
| There is code to delete duplicates, I have some buried somewhere, and I'm sure you can search forums & Google for it, but a quick and easy way is to:1) Create a "new" copy of the table, but with the correct keys.2) Alter the "new" table to "Ignore duplicate key"3) Copy from "old" table to "new" table.It will copy all the records except for duplicates.4) Truncate your "old" table.5) Copy your data from "new" table to "old" table.(Less safe) If you don't have foreign keys, triggers etc, you can:4) Drop the "old" table5) Rename your "new" table to "old" table.6) Undo "Ignore duplicate key"Of course, whatever way you go, Backup your "old" table first. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-08 : 13:30:15
|
| Want to give us some info.Follow the hint link's instructions below.sample data, DDL, expected results will help us help you.But I always find this question amusing.How do you arbitraily decide which row to blow away?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-08 : 13:40:22
|
| Besides searching the forums to see if this question has been asked before, you should also always search the SQLTeam articles as well. Here's the deleting duplicates article:http://www.sqlteam.com/item.asp?ItemID=3331You can do the search from the main page in the top right hand side.Tara |
|
|
|
|
|
|
|
|
remove duplicates - delete a single row