MySQL INSERT if not exist (not using primary key)

Question

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$$

the question is if the row already exists what do you want to do? — jcho360, Jul 06 '12 at 20:20
@jcho360 yes, needs to be replaced with a command to do nothing — t q, Jul 06 '12 at 20:22
I think you need to look at what ON DUPLICATE KEY associates with for column(s). As well as INSERT IGNORE, I don't think it's doing what you think it's doing. — JohnP, Jul 06 '12 at 20:24
@JohnP not sure what you mean but just a reminder that these 2 columns `myid`, `theirid` are not primary keys — t q, Jul 06 '12 at 20:26
Which is why I said look at it again. on duplicate key is used when a primary key or indexed field is duplicated, not when a couple random columns are duplicated. It's pretty much irrelevant to the way you are doing it now. — JohnP, Jul 06 '12 at 20:28

ypercubeᵀᴹ · Accepted Answer · 2012-07-06T21:06:43.323

12

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

edited Jul 06 '12 at 21:06

answered Jul 06 '12 at 20:38

ypercubeᵀᴹ

105,605
14
160
222

What columns does the table have? Please add the `SHOW CREATE TABLE mytable` output in your question. – ypercubeᵀᴹ Jul 06 '12 at 20:49
Did you try the 2nd query (and did you put the two values (5 and 2) in both places? – ypercubeᵀᴹ Jul 06 '12 at 20:53
1

dont get an error, i have this in php ini_set('display_errors',1); error_reporting(E_ALL|E_STRICT); – t q Jul 06 '12 at 21:00
1

what is `dual` table? – Newbie May 21 '18 at 00:46

score 1 · Answer 2 · answered Jul 06 '12 at 20:29

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.

answered Jul 06 '12 at 20:29

jcho360

3,665
1
12
23

my primary key is set to auto increment, i wouldnt update it like this – t q Jul 06 '12 at 20:32
The point is that on duplicate key is designed to work IF you ARE updating the primary key. Since you aren't, there is no need to have it in your query, which is why I said it was irrelevant to what you are doing. – JohnP Jul 06 '12 at 20:40

score 1 · Answer 3 · edited Aug 07 '13 at 17:34

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

score 0 · Answer 4 · answered Jul 06 '12 at 20:27

0

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.

answered Jul 06 '12 at 20:27

Battle_707

698
5
15

i have a `id` column that autocounts as the primary key. if so what would the SQL command look like? – t q Jul 06 '12 at 20:28
1

SELECT COUNT(*) as total FROM your_table WHERE column 1 = '5' and column2 = '1'; Then if total = 0, insert otherwise do nothing. I think you could combine it all in one query, but that syntax I'm not sure of. – JohnP Jul 06 '12 at 20:43
1

@tq: I apologize, for my late reply. Some scripts weren't loading, so the site didn't work for me for a while. What I assumed is that you have a join table with a PK, myid, and yourid. If you add a unique clustered index on myid,yourid to prevent double inserts. – Battle_707 Jul 07 '12 at 02:02
would that work if i had repeating values like (5,1) (5,2) (5,3) – t q Jul 07 '12 at 03:55
1

@tq: If you index it as a cluster, values like (5,1), (5,2) will not trigger a duplicate error. If you create your unique index incorrectly, then it will throw that error sooner. It's easy to check if you build your index corrently, as it will likely not even allow you to build the index if you do it wrong. – Battle_707 Jul 07 '12 at 18:55

MySQL INSERT if not exist (not using primary key)

4 Answers4

Linked