CODEWITHSUNDEEP
mysql
265
265

Reputation: 143

MySQL INSERT IGNORE or ON DUPLICATE KEY DO_NOTHING

already searched for such topics and found 2 different solutions but noone works.

My table has structure | ID (auto_increment primary_key) | UID (int) | FAV_ID (int) |

I need to insert new record to this FAV_TABLE if UID and FAV_ID (both) already exist.

Example of my query: 

INSERT INTO FAV_TABLE (uid, fav_id) VALUES ($u_id,$s_id) ON DUPLICATE KEY UPDATE  uid = uid

or this one

INSERT IGNORE FAV_TABLE (uid, fav_id) VALUES ($u_id,$s_id);

As mysql manuals says this query doesn't add record only if PRIMARY_KEY is the same. And I need query not to add record if pair uid+fav_id is unique. Any solutions? Thank you

Upvotes: 6

Views: 11475

Answers (2)

eggyal
eggyal

Reputation: 125835

You need to add a UNIQUE KEY on those columns:

ALTER TABLE FAV_TABLE ADD UNIQUE KEY(uid, fav_id);

Upvotes: 5

Stelian Matei
Stelian Matei

Reputation: 11623

INSERT IGNORE or ON DUPLICATE KEY works only when a unique key is duplicated.

You need to add a UNIQUE index on both fields uid and fav_id. That way, when you insert a duplicate pair, it will be ignored.

Upvotes: 2

Related Questions