How to UPDATE a column of all duplicate records in MySQL?

Question

For e.g., I have a MySQL table named "Qbank" which has following columns

• ID (int AUTO_INCREMENT)
• Question (longtext)
• Repeated (int, default 0)

I know how to get counts of repeated rows (Questions) -

SELECT Question,
       Repeated,
       count(ID) as cnt
FROM Qbank
GROUP BY Question
HAVING cnt > 1

And also, to get a list of all duplicate rows -

SELECT ID, Qbank.Question, Repeated FROM Qbank 
INNER JOIN (
    SELECT Question
    FROM Qbank 
    GROUP BY Question
    HAVING count(ID) > 1
) dup ON Qbank.Question = dup.Question 
ORDER BY Question

But what I want to do is - to SET the "Repeated" value of all the duplicates (having same Question) to 1.
I.e. so that these can be differentiated from remaining non-duplicate questions, which have Repeated value 0 by default.

What would be the structure of such query?

Sorry, for the elaboration, that was the only way to prove, that I have searched a lot before asking, and have not found anything yet.

Thanks.

Regards,
Dr. Atul

Answer 1

Try using JOIN in UPDATE:

UPDATE Qbank T1 JOIN 
  (SELECT Question FROM Qbank 
   GROUP BY Question HAVING count(ID) > 1) dup ON T1.Question = dup.Question 
SET T1.Repeated = 1