Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

How can I custom dedup a table?

I have a table of users that I want to de-duplicate and put into another table. But I want to have control over which of the duplicates I want to keep. Here's what I'm trying to do (in Access 2003):

TableA:
username        password       customerid
tom                   secret              987873
tom                   mystery           843399
jane                  love                  549989
mary                blue                  432239
mary                red                    534989

I want to end up with TableB:
username        password       customerid
tom                   secret              987873
jane                  love                  549989
mary                red                    534989

What I'm trying to do is pick the record that has the max customerid and get rid of the other duplicate. How can I do this with an INSERT/SELECT sql query?
0
bemara57
Asked:
bemara57
2 Solutions
 
David ToddSenior DBACommented:
Hi,

I did this on SQL Server 2005 for illustration and testing ...

use tempdb
go

if object_id( N'tempdb..#Duplicates', N'U' ) is not null
      drop table #Duplicates;
      
create table #Duplicates
      (
      username varchar( 20 )
      , password varchar( 20 )
      , customerID int
      )
      
insert into #Duplicates values( 'Tom', 'secret', 987873 )
insert into #Duplicates values( 'Tom', 'mystery', 843399 )
insert into #Duplicates values( 'Jane', 'love', 549989 )
insert into #Duplicates values( 'Mary', 'blue', 432239 )
insert into #Duplicates values( 'Mary', 'red', 534989 )

select *
from #Duplicates

select d.*
from #Duplicates d
where d.CustomerID in
      (
      select max( di.CustomerID )
      from #Duplicates di
      group by di.username
      )
      
if object_id( N'tempdb..#Duplicates', N'U' ) is not null
      drop table #Duplicates;

HTH
  David
0
 
kemanetzisCommented:
and to also have the insert select statement
(i assume u also create an empty #NoDuplicates)

insert into #NoDuplicates
select * from #Duplicates d
where d.CustomerID in
      (
      select max( di.CustomerID )
      from #Duplicates di
      group by di.username
      )

this will fill your no duplicates table
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now