0

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.'"
))'
Eric T
  • 1,008
  • 3
  • 17
  • 39

2 Answers2

11

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);
Jürgen Steinblock
  • 26,572
  • 21
  • 100
  • 169
  • Hi there your answer was something that I'm looking at but is it possible that the ip_address repeat more than once but in a different product id. How can I do that? – Eric T Jun 20 '13 at 06:58
  • 1
    You can have a unique index for the combination product/ip_address, this allows you to have an ip_address assigned to multiple products but ensures that you can't insert the same ip_address for a product twice. I added the statement to modify your table to my answer – Jürgen Steinblock Jun 20 '13 at 07:04
  • Hi again, this was just perfect. One more thought, I was using INSERT IGNORE and when the same product_id and ip_address being insert it does ignore, but the id itself incremented by 1. Is that the normal behavior? – Eric T Jun 20 '13 at 07:27
  • 1
    Seems to be a bug/or feature? in InnoDB. Look here: http://stackoverflow.com/questions/5655396/why-insert-ignore-increments-the-auto-increment-primary-key on how to avoid this. I wasn't aware about this. You also could make `product_id+ip_address` your primary key, and drop the Autoincrement column. – Jürgen Steinblock Jun 20 '13 at 08:00
  • Thanks, for your guides and helps. Appreciate it. – Eric T Jun 20 '13 at 08:27
2

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'));
ypercubeᵀᴹ
  • 105,605
  • 14
  • 160
  • 222
Romesh
  • 2,214
  • 2
  • 20
  • 43