How do I mark duplicates in a given group in MySQL?

Question

I have the following table structure (in MySQL):

DocID, Code, IsDup, DopOf

, where DocID is Unique.

Values are like :

1,AAAA,nul,nul
2,AAAA,nul,nul
3,AAAA,nul,nul
4,BBBB,nul,nul
5,CCCC,nul,nul
6,CCCC,nul,nul

What I want is to write a procedure which can update the table and give the desired result as:

1,AAAA,0,0
2,AAAA,1,1
3,AAAA,1,1
4,BBBB,0,0
5,CCCC,0,0
6,CCCC,1,5

IsDup shows whether the Doc is duplicate or not on the basis of Code, and DupOf indicates the original DocId.

Can anybody help me? I'm trying to implement the logic, but I'm stuck.

Your help would be highly appreciated.

Thanks.

Answer 1

UPDATE  table t
JOIN    (
        SELECT  code, MIN(docId) AS firstdoc
        FROM    table
        GROUP BY
                code
        ) q
ON      t.code = q.code
SET     t.isDup = NOT (t.docId = q.firstdoc), 
        t.dupOf = CASE WHEH t.docId = q.firstdoc THEN 0 ELSE q.firstDoc END

If your table is MyISAM, you should have an index on (code, docId).

If your table is InnoDB and docId is a PRIMARY KEY, you should have an index on (code).