23

I have a mysql table ip_list...

+----+---------------+
| id |    ip_addr    |
+----+---------------+
|  1 | 192.168.100.1 |
|  2 | 192.168.100.2 |
|  3 | 192.168.100.3 |
|  4 | 192.168.100.4 |
|  5 | 192.168.100.5 |
+----+---------------+

I want to add only that records which are not in ip_addr column. For ex

I have following records to add in ip_addr table

192.168.100.6
192.168.100.10
192.168.100.11
192.168.100.1   //already in column
192.168.100.12

I don't want to add 192.168.100.1, because it is already in column.

So, is it possible that INSERT query first check the records then insert it?

Currently, I am doing this with.. first I SELECT the records then match it and then INSERT it.

but, I want to do with only one query.

Improve this question
9
  • is the record from the other table/. Commented Apr 9, 2013 at 9:45
  • 1
    You could add a unique index to the ip column and then you're left with INSERT .. ON DUPLICATE KEY or INSERT IGNORE Commented Apr 9, 2013 at 9:45
  • make ip_addr as unique field Commented Apr 9, 2013 at 9:45
  • 1
    adding unique constraint on the column would be a better idea, in my own opinion. Commented Apr 9, 2013 at 9:50
  • 1
    @YogeshSuthar yah thats what I want, let me check its working or not Commented Apr 9, 2013 at 9:51

5 Answers 5

Reset to default
32

You can use below query. Here it will insert the ip_address when it is not present in your table.

INSERT INTO ip_list (ip_addr)
SELECT * FROM (SELECT '192.168.100.1') AS tmp
WHERE NOT EXISTS (
    SELECT ip_addr FROM ip_list WHERE ip_addr='192.168.100.1'
);
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Ekach number yogesh bhau.
+1 upvote for your answer, thanks :)
I've wanted to do this for about 10 years but never thought to Google it. Just accepted doing 2 queries - select then insert. this is superb!
Super method. Is there a way of extending this to multiple values at one go ? For examples check if 192.168.100.1 and 192.168.100.2 exist else insert both.
9

You should add a UNIQUE key on ip_addr and then use INSERT IGNORE.

Maybe this helps if you haven't heard of UNIQUE yet: http://www.tutorialspoint.com/sql/sql-unique.htm

Improve this answer

1 Comment

+1 upvote for your answer, thanks
6

Try MySQL INSERT IGNORE statement.

Improve this answer

3 Comments

+1 upvote for your answer, thanks
this is better option ,because we does not need more computation.
INSERT IGNORE INTO tableName
4

if I were you, I enforce a UNIQUE constraint on the column,

ALTER TABLE ip_list ADD CONSTRAINT IP_Unique UNIQUE(ip_addr)
Improve this answer

1 Comment

+1 upvote for your answer, thanks
4

you can do that with Insert...on duplicate key update

OR

alternatively you can also be use Replace

Improve this answer

1 Comment

+1 upvote for your answer, thanks

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.