MySQL INSERT if not exist (not using primary key)

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

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.

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` ;