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

php mysql sql sql-insert

28,831

Solution 1

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

Solution 2

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

Solution 3

Try MySQL INSERT IGNORE statement.

Solution 4

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

alternatively you can also be use Replace

Solution 5

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

ALTER TABLE ip_list ADD CONSTRAINT IP_Unique UNIQUE(ip_addr)

View more solutions

28,831

Sumit Bijvani

Web Developer at WDS India. Sites WDS India Facebook Profile Twitter Account profile for Sumit Bijvani on Stack Exchange, a network of free, community-driven Q&A sites http://stackexchange.com/users/flair/1093989.png

Updated on April 09, 2020

Comments

Sumit Bijvani about 4 years
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.
- John Woo about 11 years
  
  is the record from the other table/.
- Dale about 11 years
  
  You could add a unique index to the ip column and then you're left with INSERT .. ON DUPLICATE KEY or INSERT IGNORE
- Suhel Meman about 11 years
  
  make ip_addr as unique field
- Manoj Purohit about 11 years
  
  can't you simply add unique index to ip_addr
- Sumit Bijvani about 11 years
  
  @all I know about unique key
- Sumit Bijvani about 11 years
  
  @JW can I do this without unique key?
- John Woo about 11 years
  
  adding unique constraint on the column would be a better idea, in my own opinion.
- Sumit Bijvani about 11 years
  
  @YogeshSuthar yah thats what I want, let me check its working or not
- not2qubit over 10 years
  
  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.
Freelancer about 11 years

Ekach number yogesh bhau.
Sumit Bijvani about 11 years

+1 upvote for your answer, thanks
Sumit Bijvani about 11 years

+1 upvote for your answer, thanks
Sumit Bijvani about 11 years

+1 upvote for your answer, thanks
Sumit Bijvani about 11 years

+1 upvote for your answer, thanks :)
Sumit Bijvani about 11 years

+1 upvote for your answer, thanks
Sumit Bijvani about 11 years

@YogeshSuthar thanks again :)
James Wilson about 8 years

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!
HopeKing almost 7 years

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.
Oscar Zarrus over 4 years

Replace, what a great invention! Thanks
Harikrushna Patel about 2 years

this is better option ,because we does not need more computation.
Harikrushna Patel about 2 years

INSERT IGNORE INTO tableName