Solved

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

Posted on 2010-09-23
4
387 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 45

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 125 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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