CODEWITHSUNDEEP
mysqlsqlinsert
James Dawson
James Dawson

Reputation: 5409

Only inserting row when record doesn't already exist

The table is called limits and takes the following fields:

id    |    ip    |    limit

on every page load, I'm checking if the user has a record in this table (determined by ip) and if they don't, I insert a record. However if the record already exists, I don't want to do anything.

I basically need a INSERT IF NOT EXISTS type query. I've already read this and the INSERT IGNORE example looks good but as far as I can tell it only works for primary keys. In my case the id field is the primary key and I want to check if the ip already exists.

Here's some sort of pseudo SQL code:

INSERT INTO `limits` (ip, limit)
VALUES ('127.0.0.1', 8)
IF NOT EXISTS `limits`.`ip`

Thanks for any help!

EDIT: tried this too

INSERT IGNORE INTO `limits`
SET `ip` = ‘127.0.0.1′,
`limit` = 8;

Upvotes: 1

Views: 1943

Answers (2)

ESG
ESG

Reputation: 9435

INSERT IGNORE will ignore any unique indexes, whether it's the primary key or any unique key.

You can create a unique key on IP using

ALTER TABLE limits
  ADD CONSTRAINT idx_limits_ip
  UNIQUE (ip)

The Manual has a few alternative syntax to create indexes.

Upvotes: 2

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

INSERT INTO `limits` (ip, limit)
SELECT '127.0.0.1', 8
WHERE NOT EXISTS (SELECT * FROM `limits` WHERE `limits`.`ip` =  '127.0.0.1')

Upvotes: 0

Related Questions