MySql query for filling table field with data from other table field with NO duplicates

I am using mySQL and have two tables (from_tbl, to_tbl). The from_tbl has four columns, I need to copy two of those into to_tbl. With no duplicates.

mysql> select * from frm_tbl;
+----+---------+---------------------------+-----------------+------------------------------+---------+
| id | symbol  | name                      | sector          | industry                     | country |
+----+---------+---------------------------+-----------------+------------------------------+---------+
|  1 | AAPL    | Apple Computer Inc.       | Technology      | Personal Computers           | US      |
|  2 | AAPL_EUR| Apple Computer Inc.       | Technology      | Personal Computers           | US      |
|  3 | CEO     | CNOOC Ltd. (ADS)          | Basic Materials | Independent Oil & Gas        | US      |
|  4 | CEO_EUR | CNOOC Ltd. (ADS)          | Basic Materials | Independent Oil & Gas        | US      |
|  5 | GE      | General Electric Company  | Conglomerates   | Conglomerates                | US      |

SHOW CREATE TABLE from_tbl;

| import_company | CREATE TABLE `import_company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbol` varchar(10) NOT NULL COMMENT 'raw symbol',
  `name` varchar(50) NOT NULL DEFAULT 'NA' COMMENT 'long name of company',
  `sector` varchar(45) NOT NULL DEFAULT 'NA' COMMENT 'sector description',
  `industry` varchar(45) NOT NULL DEFAULT 'NA' COMMENT 'industry description',
  `country` varchar(2) NOT NULL DEFAULT 'NA' COMMENT '2 letter code, location of exchange',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 |

SHOW CREATE TABLE to_tbl;

| sector_industry | CREATE TABLE `sector_industry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sector` varchar(45) NOT NULL DEFAULT 'N/A',
  `industry` varchar(45) NOT NULL DEFAULT 'N/A',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 |

I have tried:
### this kinda works (no dupes) initially, but on re-use then creates duplicates
INSERT INTO to_tbl (sector, industry)
SELECT DISTINCT sector, industry
FROM frm_tbl
;

I have spent the last 4 hours trying to figure this out.
What I need to know is, what is the mySQL script that will import/copy over the data in 'sector' and 'industry' in the frm_tbl and not create any duplicates in to_tbl? I want to avoid this:

| Technology      | Personal Computers           | US      |
| Technology      | Personal Computers           | US      |

I have tried making 'sector' and 'industry' in the to_tbl UNIQUE but I am stumped.
I do not want to make two more tables (sector_tbl, industry_tbl).
Thank you in advance.
LVL 1
John_2357Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
try this
INSERT INTO to_tbl (sector, industry)
SELECT DISTINCT t0.sector, t0.industry
FROM frm_tbl t0
LEFT JOIN to_tbl t1
  ON t0.sector = t1.sector
 AND t0.industry = t1.industry
WHERE t1.sector is NULL;

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi John,

You need to us DISTINCT on the source table (as you've done) and compare the results with the items in the destination table by joining them.


The code below should do the trick.  :)


Good Luck,
Kent

INSERT INTO to_tbl (sector, industry)
SELECT DISTINCT sector, industry
FROM frm_tbl t0
LEFT JOIN to_tbl t1
  ON t0.sector = t1.sector
 AND t0.industry = t1.industry
WHERE t1.sector is NULL;

Open in new window

0
 
John_2357Author Commented:
Hi Kent,

Thank you for the fast reply. I am getting this error:
ERROR 1052 (23000): Column 'sector' in field list is ambiguous

Thank you for your help,
John
0
 
John_2357Author Commented:
Thanks for the fast reply.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.