Insert query check if record exists - If not, Insert it [duplicate]

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.

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