Solved

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

Posted on 2010-09-23
4
366 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
  • 2
4 Comments
 
LVL 45

Expert Comment

by:Kdo
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 40

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

825 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