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 |
|
randomman
Starting Member
1 Post |
 Posted - 2012-03-13 : 07:16:56
|
| I have a table within a database which i need to patch. Duplicate items have been added to the table and i need to write a sql script to remove the duplicates.The fields ID - Primary Keyuserid – unique to the user order_type - the type of order placedorder_active – is the order active a value of 1 or 0 I have a problem where i have duplicate records. In my case duplicate records are ones which have matching userid and order_type. However if i have several duplicates, I wish to keep the one which is marked as order_active, if none of the duplicates are marked as order_active then i can just keep any one of the records.Any help on this would be greatly appreciated. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-13 : 07:34:00
|
| This should show you the records you don't want to keep, trivial to then use this within a delete statementSELECT ID FROM(SELECT ID, Row_Number() Over (Partition by UserID, Order_Type Order By Order_Active DESC) AS RowNo FROM <Whatever the table is called>) subWHERE RowNo > 1Untested since I have neither table definition nor sample data--Gail ShawSQL Server MVP |
 |
|
|
whiteblue
Starting Member
4 Posts |
Posted - 2012-03-13 : 10:54:12
|
| 1)select distinct * into temororytable from old table.2)delete new temporory table.3)insert oldtable select * from tempororytable |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-13 : 11:53:53
|
quote:
Originally posted by whiteblue
1)select distinct * into temororytable from old table.2)delete new temporory table.3)insert oldtable select * from tempororytable
Won't work because it's not full rows that are duplicate, there are two columns out of the 4 that are 'duplicate'--Gail ShawSQL Server MVP |
|
|
|
veeraC85
Starting Member
2 Posts |
|
|
|
|
|
Delete dupliactes