Insert query check if record exists - If not, Insert it

Question

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.

You could add a unique index to the ip column and then you're left with `INSERT .. ON DUPLICATE KEY` or `INSERT IGNORE` — Dale, Apr 09 '13 at 09:45
adding unique constraint on the column would be a better idea, in my own opinion. — John Woo, Apr 09 '13 at 09:50
@YogeshSuthar yah thats what I want, let me check its working or not — Sumit Bijvani, Apr 09 '13 at 09:51
How could you do this without having to make **ip_addr** column unique? @casperOne: This is NOT a duplicate question. What you linked to requires the use of a _primary_ key. THIS question does not. — not2qubit, Oct 29 '13 at 16:39

Yogesh Suthar · Accepted Answer · 2013-04-09T09:59:01.947

24

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'
);

edited Apr 09 '13 at 09:59

answered Apr 09 '13 at 09:46

Yogesh Suthar

29,554
17
66
96

1

Ekach number yogesh bhau. – Freelancer Apr 09 '13 at 09:48
+1 upvote for your answer, thanks :) – Sumit Bijvani Apr 09 '13 at 09:56
2

@YogeshSuthar thanks again :) – Sumit Bijvani Apr 09 '13 at 10:00
1

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! – James Wilson Apr 05 '16 at 08:17
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. – HopeKing May 22 '17 at 05:41

score 8 · Answer 2 · answered Apr 09 '13 at 09:45

8

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

answered Apr 09 '13 at 09:45

mrks

6,996
26
48

+1 upvote for your answer, thanks – Sumit Bijvani Apr 09 '13 at 09:55

score 4 · Answer 3 · answered Apr 09 '13 at 09:46

4

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

ALTER TABLE ip_list ADD CONSTRAINT IP_Unique UNIQUE(ip_addr)

answered Apr 09 '13 at 09:46

Skinny Pipes

1,005
6
14

+1 upvote for your answer, thanks – Sumit Bijvani Apr 09 '13 at 09:57

score 4 · Answer 4 · answered Apr 09 '13 at 09:46

4

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

OR

alternatively you can also be use Replace

answered Apr 09 '13 at 09:46

Suresh Kamrushi

13,699
12
70
84

+1 upvote for your answer, thanks – Sumit Bijvani Apr 09 '13 at 09:54
1

Replace, what a great invention! Thanks – Oscar Zarrus Jan 25 '20 at 09:21

score 3 · Answer 5 · answered Apr 09 '13 at 09:47

3

Try MySQL INSERT IGNORE statement.

answered Apr 09 '13 at 09:47

Peter Kiss

9,153
2
21
38

+1 upvote for your answer, thanks – Sumit Bijvani Apr 09 '13 at 09:55

Insert query check if record exists - If not, Insert it

5 Answers5

Linked