Have Geek Brain

How to mark duplicates in a given group in MySQL?

I have the following table structure (in MySQL):

DocID, Code, IsDup, DopOf

where DocID

is unique.

Values:

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

I want to write a procedure that can update the table and give the desired result:

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

indicates whether a character is Doc

repeated or not based on Code

, and DupOf

indicates the original DocID

.

Can anyone help me? I am trying to implement the logic, but I am stuck.

Your help will be much appreciated.

Thanks.

+1


a source to share


1 answer 


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 PRIMARY KEY

, you must have an index on (code)

.

+3


a source






More articles:


All Articles