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 |
|
MBeal
Posting Yak Master
110 Posts |
 Posted - 2009-09-02 : 14:14:42
|
| I have a table called Customer with Rec_ID, name, address, city, state, zip, and a few other details.I created another table called Dup_Cust with the same fields but added a field called DupeGrp. I identified each of the records that are duplicated and inserted them into the DupeGrp table. So now I have Customer with all records and Dup_Cust with only records that are in Customer that have at 1 or more other records matching on the address fields (address, city, state, zip).Now, I would like to update the Dup_Cust table and identify one of the records in a duplicate group of records as the primary record [ MIN(Rec_ID) ], and update the DupeGrp field for each of those records that share that same address as the primary. In the end I should have in the DupeGrp field the Rec_ID of the primary customer for each of the customers sharing that same address. (Multiple groups of duplicates, multiple Rec_ID's -- so if I did a distinct Rec_ID I would find fewer records (only the primary customers). Not certain how do write the update statement? Any suggestions?MBeal |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-09-02 : 14:24:18
|
| something silimar to this?UPDATE TSET T.REC_ID = T1.REC_IDFROM DUP_CUST TJOIN (SELECT MIN(REC_ID) AS REC_ID, ADDRESS FROM DUP_CUST GROUP BY ADDRESS) AS T1ON T.ADDRESS = T1.ADDRESS |
 |
|
|
eralper
Yak Posting Veteran
66 Posts |
Posted - 2009-09-02 : 14:26:33
|
| Hi MBeal,You can check the article which orders each dublicate content and sorts by using Row Number using Partition hint at [url]http://www.kodyaz.com/articles/delete-duplicate-rows-using-row-number-partition-by-order-by.aspx[/url] and you can also reference to the [url]http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx[/url] if the rows are totally identical-------------Eralperhttp://www.kodyaz.com |
|
|
|
MBeal
Posting Yak Master
110 Posts |
Posted - 2009-09-02 : 14:33:33
|
| That worked wonderfully! THANK YOU!!!MBeal |
|
|
|
|
|
|
|
|
Identifying Duplicate Records