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

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

AdrianSmithUKAsked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
INSERT IGNORE INTO `ip` (`ip`) SELECT DISTINCT (`ip`) FROM  `log` WHERE `ip` NOT IN (SELECT `ip` FROM ip);
0
 
theGhost_k8Database ConsultantCommented:
INSERT IGNORE INTO `ip` SELECT DISTINCT (`ip`) FROM  `log` WHERE `ip` NOT IN (SELECT `ip` FROM ip);
0
 
AdrianSmithUKAuthor Commented:
hi theGhost_k8

I get this error.

#1136 - Column count doesn't match value count at row 1
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
AdrianSmithUKAuthor Commented:
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
 
Shiv_SgConnect With a Mentor Commented:
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
 
AdrianSmithUKAuthor Commented:
Many thanks chaps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.