?
Solved

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

Posted on 2010-09-23
4
Medium Priority
?
396 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:John_2357
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 33748886
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
 
LVL 1

Author Comment

by:John_2357
ID: 33748980
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
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 33749692
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
 
LVL 1

Author Closing Comment

by:John_2357
ID: 33750280
Thanks for the fast reply.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question