Is there an efficient way to insert only if there's no row with the same values in two columns (without subquery)?

Question

I know I can do an:

INSERT INTO MyTable (name, num, dateCreated)
VALUES ( "Joe", 31, "2016-05-01 00:00:10" )
WHERE NOT EXISTS (
    SELECT num FROM MyTable WHERE num = 31 AND dateCreated = "2016-05-01 00:00:10"
)

But this is very slow as it uses a sub-query on every insert. Is there a faster way to do this?

Can i add a unique constraint on multiple columns?

I do NOT want to use the primary key to test for duplicate

http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — juergen d, Jun 10 '16 at 15:54
Possible duplicate of [INSERT if not Exist Mysql](http://stackoverflow.com/questions/20402786/insert-if-not-exist-mysql) — Jocelyn, Jun 10 '16 at 15:55

score 1 · Accepted Answer · answered Jun 10 '16 at 17:56

1

You can make unique index which is a combination of your 2 columns. ALTER TABLE students ADD UNIQUE idx_row_unique(first_name,last_name,...);

answered Jun 10 '16 at 17:56

Vivek Pratap Singh

9,326
5
21
34

Is there an efficient way to insert only if there's no row with the same values in two columns (without subquery)?

1 Answers1