CODEWITHSUNDEEP
mysqlsqldistinct
iks_in
iks_in

Reputation: 133

Insert distinct records of one table to another into MySQL

Table 1:mydata_table
Multiple Columns. But I want to fetch 3 columns' distinct records.

Email_Office | Email_Personal1 | Email_Personal2

Table 2:unique_emails

Only 3 Columns. Email_Office | Email_Personal1 | Email_Personal2

I tried this code for all three columns of table 2 to insert distinct records from each column.

insert into unique_emails 
(email_personal1) 
select distinct email_personal1
from mydata_table
where Email_Personal1!=""

It inserts the records but the problem is if there are 100 rows in email_office column and 300 rows in email_personal1 then it will show first 100 rows with email_office and remaining two columns are empty, then from 101th row, it will show email_personal1's records and remaining two columns are empty. I want to insert all rows together. How can I do that?

Upvotes: 1

Views: 8602

Answers (2)

serges_newj
serges_newj

Reputation: 815

If you want to insert unique triples of e-mails, you should use

insert into unique_emails 
(email_office, email_personal1, email_personal2) 
select distinct email_office, email_personal1, email_personal2
from mydata_table
where email_office!="" OR Email_Personal1!="" OR Email_Personal2!=""

But in that case some emails may occur multiple times in table unique_emails.

Supposing Table 1 consist of records:

Email_Office | Email_Personal1 | Email_Personal2
------------------------------------------------
[email protected]        [email protected]           [email protected]
[email protected]        [email protected]           [email protected]
[email protected]        [email protected]           [email protected]
[email protected]        [email protected]           NULL
[email protected]        [email protected]           [email protected]

then result Table 2 

 Email_Office | Email_Personal1 | Email_Personal2   
------------------------------------------------
[email protected]        [email protected]           [email protected]
               [email protected]           [email protected]
               [email protected]

UPD. Try this: (SQL Fiddle)

insert into unique_emails 
(email_office, email_personal1, email_personal2)
select b.email_office, c.email_personal1, d.email_personal2
from (
  select @i := @i + 1 AS pos from mydata_table, (select @i := 0) r) a
left join (
  select t.*, @j := @j + 1 AS pos
  from (select distinct email_office from mydata_table where email_office!="") t,
    (select @j := 0) r) b on b.pos = a.pos
left join (
  select t.*, @k := @k + 1 AS pos
  from (select distinct email_personal1 from mydata_table where email_personal1!="") t,
    (select @k := 0) r) c on c.pos = a.pos
left join (
  select t.*, @l := @l + 1 AS pos
  from (select distinct email_personal2 from mydata_table where email_personal2!="") t,
    (select @l := 0) r) d on d.pos = a.pos
where b.email_office is not null or c.email_personal1 is not null or d.email_personal2 is not null

Upvotes: 1

Rafal Kozlowski
Rafal Kozlowski

Reputation: 760

Try to creare unique index for columns that needs to be unique, and use the first SQL proposed by @Sergey with ignore:

INSERT IGNORE INTO
  unique_emails 
  (email_office, email_personal1, email_personal2) 
  SELECT DISTINCT
    email_office,
    email_personal1,
    email_personal2
  FROM
    mydata_table
  WHERE
    email_office!=""
    OR Email_Personal1!=""
    OR Email_Personal2!=""

Upvotes: 1

Related Questions