Remove rows with duplicate column data in separate columns
I have a table that we will call Table1 with a bunch of unnecessary data in it and no unique id column.
I want to select multiple columns from table 1 and transfer the data to table2. However, after the transfer, I need to delete rows from table 2 with duplicates in 3 columns.
Let's say I have a string in string2 with the columns [FirstName]
, [LastName]
, [CompanyName]
, [City]
and [State]
that has been transferred. I want to keep only rows with unique combinations [FirstName]
, [LastName]
and [CompanyName]
. To add to the confusion, [LastName]
and / or [CompanyName]
may contain NULL values. How could I do this? Thanks in advance for any help.
source to share
Unique records can be created using a keyword distinct
.
select distinct
FirstName,
LastName,
CompanyName
from MyTable
So, if you run the following command, you will only add individual 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 good way to add new values to a table is with 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 command MERGE
gives you control over when you want to sync tables.
source to share
see here for an example, maybe 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;
source to share
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 .....);
source to share