Delete Rows with Duplicate Column Data in Specific Columns

Question

I have a table we'll call Table1 with a bunch of junk data in it and no unique identifier column.

I want to select some columns from Table1 and transfer the data to Table2. However, after the transfer, I need to delete rows from Table2 with duplicates in 3 of the columns.

Let's say I have a row in Table2 with columns [FirstName], [LastName], [CompanyName], [City], and [State] that were transferred. I want only the rows with unique combinations of [FirstName], [LastName], and [CompanyName] to remain. To add to the confusion, [LastName] and/or [CompanyName] could contain NULL values. How could I accomplish this? Thanks in advance for any help.

SELECT DISTINCT [FirstName],[LastName],['CompanyName'] FROM [Table] Hav u tried this ? — Hary
– Hary, Commented Jan 20, 2013 at 9:18

alzaimar · Accepted Answer · 2013-01-20 09:32:55Z

Unique entries can be created using the distinct keyword.

select distinct 
       FirstName, 
       LastName, 
       CompanyName 
  from MyTable

So if you issue the following command, you will only add distinct values to the new table

insert into newTable
(
       FirstName, 
       LastName, 
       CompanyName 
)
select distinct 
       FirstName, 
       LastName, 
       CompanyName 
  from MyTable
 where not exists (
   select 1 from newTable 
    where newTable.FirstName   = MyTable.FirstName
      and newTable.LastName    = MyTable.LastName
      and newTable.CompanyName = MyTable.CompanyName
  )

Another nice way to add distinct new values to a table can be done by using the 'MERGE' command.

merge newtable as target
using (select distinct 
              FirstName, 
              LastName, 
              CompanyName 
         from MyTable
       ) as source
   on target.FirstName   = target.FirstName
  and target.LastName    = target.LastName
  and target.CompanyName = target.CompanyName

when not matched by target then
  insert (FirstName, 
          LastName, 
          CompanyName)
  values (target.FirstName, 
          target.LastName, 
          target.CompanyName);

The MERGE command gives you the option to control when you want to synchronize tables.

sourcecode · Accepted Answer · 2013-01-20 09:35:36Z

0

see here example , might be this is what you wanted..link

insert into Table2(`firstname` , `lastname` , `companyname`)
select a.firstname,a.lastname,a.companyname 
from 
(select distinct(concat(firstname,',',lastname,',',companyname))
,firstname,lastname,companyname from Table1) a;

edited Jan 20, 2013 at 9:35

answered Jan 20, 2013 at 9:30

sourcecode

1,8122 gold badges16 silver badges17 bronze badges

Comments

Dileep · Accepted Answer · 2013-01-20 09:36:36Z

0

create table t2
as
select distinct FirstName,LastName,CompanyName,City,State from t1;

with the below query u 'll get to know we have duplicate entries or not.
select FirstName,LastName,CompanyName,count(*) from t2
group by FirstName,LastName,CompanyName
having Count(*) >1;

delete from t2 a where rowid not in (select min(rowid) from t2 b where a.column1=b.column1 
and .....);

answered Jan 20, 2013 at 9:36

Dileep

6743 gold badges10 silver badges20 bronze badges

Collectives™ on Stack Overflow

Delete Rows with Duplicate Column Data in Specific Columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related