Solved

MySQL: How do I insert a list of unique values into a second table.

Posted on 2012-04-05
6
673 Views
Last Modified: 2012-04-05
1. This sql code gives me a list of unique ip addresses

SELECT DISTINCT (`ip`) FROM  `log` WHERE `ip` NOT IN (SELECT `ip` FROM ip);

Open in new window


2. This sql code inserts an ip address into a table.

INSERT IGNORE INTO `ip` (`ip`) VALUES ("24.24.24.24");

Open in new window


3. How can I modify this code so that it inserts all of the unique ip addresses from (1) into the ip table?

INSERT IGNORE INTO `ip` (`ip`) VALUES (SELECT DISTINCT (`ip`) FROM  `log` WHERE `ip` NOT IN (SELECT `ip` FROM ip));

Open in new window

0
Comment
Question by:AdrianSmithUK
  • 3
  • 2
6 Comments
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 37810056
INSERT IGNORE INTO `ip` SELECT DISTINCT (`ip`) FROM  `log` WHERE `ip` NOT IN (SELECT `ip` FROM ip);
0
 

Author Comment

by:AdrianSmithUK
ID: 37810143
hi theGhost_k8

I get this error.

#1136 - Column count doesn't match value count at row 1
0
 

Author Comment

by:AdrianSmithUK
ID: 37810152
I should point out that the ip table has 10+ fields and one of them is called ip.

The log table has 10+ fields and also has a field called ip.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 21

Accepted Solution

by:
theGhost_k8 earned 400 total points
ID: 37810169
INSERT IGNORE INTO `ip` (`ip`) SELECT DISTINCT (`ip`) FROM  `log` WHERE `ip` NOT IN (SELECT `ip` FROM ip);
0
 

Assisted Solution

by:Shiv_Sg
Shiv_Sg earned 100 total points
ID: 37810176
you would use the keyword VALUES only if you have static values like your query 2. but if the values are coming from another query u need to remove the VALUES keyword. So it ll be

INSERT IGNORE INTO `ip`(`ip`) (SELECT DISTINCT (`ip`) FROM `log` WHERE `ip` NOT IN (SELECT `ip` FROM `ip`))
0
 

Author Closing Comment

by:AdrianSmithUK
ID: 37810178
Many thanks chaps.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

790 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