CODEWITHSUNDEEP
mysqlsql
Eric T
Eric T

Reputation: 1026

MySQL INSERT INTO WHERE NOT EXIST

I will like to insert into same table if the ip_address for the product_id wasn't exist but preferably not to create two separate query. How can I achieve it?

Below will be the sample table:

id | product_id | ip_address |
------------------------------
a    11112        8.8.8.8
b    11234        9.9.9.9

and my code is something like this but it doesn't work.

INSERT INTO `'._DB_PREFIX_.'views` 
(SELECT '.$id_product.',"'.$ip_addr.'"
FROM `'._DB_PREFIX_.'views` 
WHERE NOT EXISTS (SELECT * FROM
`'._DB_PREFIX_.'views` v WHERE v.id_product ='.$id_product.' AND
t.ip_address ="'.$ip_addr.'"
))'

Upvotes: 0

Views: 15733

Answers (2)

Romesh
Romesh

Reputation: 2274

You can test the solution in the SQL-Fiddle.

Below is the sample Insert statement.

insert into temp
(select 2,101,'2.2.2.2'
from dual
where not exists (select * from Temp t 
              where t.product_id = 101 and
                    t.ip_address = '2.2.2.2'));

Upvotes: 2

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31723

You can either use INSERT IGNORE or REPLACE or INSERT ... ON DUPLICATE KEY UPDATE ...

Each requires you to have a unique constraint on product_id, ip_address

INSERT IGNORE

INSERT IGNORE INTO products VALUES (null, 111, '8.8.8.8')

will ignore the insert, if entry already exists.

REPLACE

REPLACE INTO products VALUES (null, 111, '8.8.8.8')

will perform a delete plus a new insert.

INSERT ... UPDATE

INSERT INTO products VALUES (null, 111, '8.8.8.8')
ON DUPLICATE KEY UPDATE products SET last_modified = NOW()

Will try to insert and if that fails update the existing record.

In your case I think you should be fine with INSERT IGNORE, however If you want to change other values if the record already exists, INSERT ... UPDATE ... should work well.

I generally would not recommend to use REPLACE unless you really want a DELETE FROM table WHERE ... plus INSERT INTO TABLE ...

Update

This requires (for this example) a unique index for the combination product, ip_address. You can achive this with

ALTER TABLE products
ADD UNIQUE INDEX `UIDX_PRODUCT__IP_ADRESS`(product, ipaddress);

Upvotes: 14

Related Questions