Solved

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

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL limit and not so limited 13 60
Duplicated data in GROUP_CONCAT 2 50
MySQL Persistent Connections 10 54
Dynamic Table mySQL stored procedure 5 62
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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