CODEWITHSUNDEEP
phpmysqlsql
Didier K
Didier K

Reputation: 32

SQL: Check for duplicates in 2 columns

I'm building a web application where users can answer questions. I'm trying to run a query where these answers are inserted in a table but where a user can only reply once the same question. In my case the query must check on duplicates at question_id & reply_user. (reply_id is already defined as the primary key).

For example when I have in my table answers : question_id = 1 & reply_user = John, John cannot reply anymore on question_id 1. But another user can of course.

I'm currently running this:

INSERT INTO replies (question_id, reply_user, reply_content, reply_anwer)
  VALUES (:questionid, :replyuser, :replycontent, :replyanswer)
  SELECT question_id, reply_user FROM replies WHERE NOT EXISTS (
    SELECT question_id FROM replies
      WHERE question_id = question_id AND reply_user = reply_user
  )

I tried out with WHERE NOT EXISTS but I couldn't find a solution with that.

Thank's for your help

Upvotes: 0

Views: 60

Answers (2)

Gumma Mocciaro
Gumma Mocciaro

Reputation: 1215

create a index unique on the two keys

ALTER TABLE replies ADD UNIQUE `preventDoubleAnswer` (`question_id`, `reply_user`) COMMENT '';

and insert the data as:

INSERT IGNORE ...

The INSERT IGNORE will insert a new data only if the unique key is satisfied. NB: is the IGNORE param is omitted, it will throw a MYSQL error

Upvotes: 0

ops
ops

Reputation: 2049

You need to unique 2 filed:

ALTER TABLE `replies` ADD UNIQUE (`question_id`,`reply_user`);

And then use this query:

INSERT INTO replies (question_id, reply_user, reply_content, reply_anwer)
VALUES (:questionid, :replyuser, :replycontent, :replyanswer)

IF question_id , reply_user exist query not run else run.

Upvotes: 1

Related Questions