CODEWITHSUNDEEP
mysqlsql
s_p
s_p

Reputation: 4693

MySQL INSERT if not exist (not using primary key)

how can i insert into a row if the pair does not exist?
* NOTE these are not primary keys, my primary KEY is set to auto increment

tried insert ignore but did not work 

INSERT IGNORE INTO mytable (`myid`, `theirid`) VALUES ('5', '1')
ON DUPLICATE KEY <DO NOTHING>

table looks like:

CREATE TABLE `mytable` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `myid` bigint(20) NOT NULL,
    `theirid` bigint(20) NOT NULL,
    `activated` tinyint(1) NOT NULL DEFAULT '0',
    `dateStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$

Upvotes: 10

Views: 19341

Answers (4)

Pere Noel
Pere Noel

Reputation: 9

Duplicate exeption ( 1062) is only raised on keys if its indexed as unique it dosent raises the exeption witch is catched by "ON DUPLICATE KEY" (ie 1062)

what you can do is add a compounded key UNIQUE(myid,theirid) if you want to raise exception when both are togetter or just add them as seperate if you want either or

but i am unsure of the syntax so just look it up but it would look like this ... 

CREATE TABLE `mytable` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `myid` bigint(20) NOT NULL,
    `theirid` bigint(20) NOT NULL,
    `activated` tinyint(1) NOT NULL DEFAULT '0',
    `dateStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),  UNIQUE(`a`,`b`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$

INSERT myid, theirid VALUES ('4' , '1') into mytable 
ON DUPLICATE KEY 
UPDATE `myid`,`theirid` ;

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

1) Can you add a UNIQUE constraint on (myid, theirid)? If yes, add this constraint and use:

INSERT INTO mytable (myid, theirid) 
  VALUES (5, 1) ;

and ignore the produce warnings (or replace the above with INSERT IGNORE)

2) If you can't add such a constraint (e.g. you sometimes want to allow such duplicates and other times you don't), you can use this:

INSERT INTO mytable (myid, theirid) 
  SELECT 5, 1 
  FROM dual 
  WHERE NOT EXISTS
        ( SELECT *
          FROM mytable
          WHERE myid = 5
            AND theirid = 1
        ) ;

Upvotes: 13

jcho360
jcho360

Reputation: 3759

I think you are not clear about the on duplicate does.... The Idea to use it is If the field is duplicate it, replace the old one for a new one or just change the Primary Key to make it UNIQUE... You can not do, the on duplicate do nothing because by Default it wont do nothing, it wont insert. Take a look here

mysql> insert into t2 values (20000,'a','a',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (20000,'a','a',0);
ERROR 1062 (23000): Duplicate entry '20000' for key 'PRIMARY'

so it did 'nothing'. If you want to know if the value exists you should use a Select statement before the Insert.

Upvotes: 1

Battle_707
Battle_707

Reputation: 708

Depending on how often you run this query (and some other environmental elements), but I would say it would make sense to create a unique index on those fields. That way, if they are duplicates, mysql will simply return an duplicate error code and not add it to the table. In addition, it will make this query execute a LOT faster, considering it doesn't have to check the entire database if the inserted values are duplicates.

Upvotes: 0

Related Questions