SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Find duplicate rows

    I need to search for duplicates - table as follows:
    Code:
    mysql> SELECT
    	COUNT(*) c,
    	theTel,
    	theDate
    FROM
    	`doTable`
    GROUP BY
    	theTel,
    	theDate
    HAVING
    	c > 1;
    
    +---+-----------+------------+
    | c | theTel    | theDate    |
    +---+-----------+------------+
    | 2 | 2107710   | 2012-08-03 |
    | 2 | 290117984 | 2011-08-08 |
    | 2 | 400018758 | 2009-10-02 |
    +---+-----------+------------+
    3 rows in set
    I need to set value to 1 for duplicate records.
    I tried this:
    Code:
    UPDATE `doTable` query1
    JOIN (
           SELECT
    	COUNT(*) c,
    	theTel,
    	theDate
    FROM
    	`doTable`
    GROUP BY
    	theTel,
    	theDate
    HAVING
    	c > 1;
    ) AS query2 ON query1.theTel = query2.theTel
    SET query1.dual = 1;
    but it updated ALL the records that where duplicate.
    E.g.:
    Code:
    +-----------+------------+-------+
    | theTel    | theDate    | Dual  |
    +-----------+------------+-------+
    | 400018758 | 2009-10-02 |  1    |
    | 400018758 | 2009-10-02 |  1    |
    +-----------+------------+-------+
    Instead I want to update only one of the duplicate records:
    Code:
    +-----------+------------+-------+
    | theTel    | theDate    | Dual  |
    +-----------+------------+-------+
    | 400018758 | 2009-10-02 |  0    |
    | 400018758 | 2009-10-02 |  1    |
    +-----------+------------+-------+
    Any help be would appreciated, thank you.

  2. #2
    SitePoint Member
    Join Date
    Oct 2013
    Location
    Warsaw, Poland
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Miguel,

    I can see two solutions to your problem:
    1) add an identity column and add in having close id=max(id)
    2) use a stored procedure. Logic would be like this:
    a) select distinct thetel,thedate in a temp table
    b) loop through the time table using a while loop:
    at each iteration:
    get thetel, thedate in a parameter
    set rowcount 1
    execute the update statement (in the where clause, thetel,thedate equals the parameters you got)

    Hope it helps

    Vincent

  3. #3
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I agree that you need a primary key/unique id. Without that I don't think you can accomplish this without some recursive method (eg php script, stored procedure).
    Code:
    alter table doTable add id bigint unsigned auto_increment, add primary key(id);
    Code:
    SELECT
    	COUNT(*) c, MIN(id) firstId,
    	theTel,
    	theDate
    FROM
    	`doTable`
    GROUP BY
    	theTel,
    	theDate
    HAVING
    	c > 1;
    Code:
         c  firstId  theTel      theDate     
    ------  -------  ----------  ------------
         2        1  2107710     2012-08-03  
         2        3  290117984   2011-08-08  
         2        5  400018758   2009-10-02
    Code:
    UPDATE `doTable` query1
    JOIN (
           SELECT
    	COUNT(*) c, MIN(id) firstId,
    	theTel,
    	theDate
    FROM
    	`doTable`
    GROUP BY
    	theTel,
    	theDate
    HAVING
    	c > 1
    ) AS query2 ON query1.theTel = query2.theTel AND query1.id!=query2.firstId
    SET query1.dual = 1;
    Code:
    select * FROM doTable;
    
    theTel      theDate         id    dual  
    ----------  ----------  ------  --------
    2107710     2012-08-03       1    (NULL)
    2107710     2012-08-03       2         1
    290117984   2011-08-08       3    (NULL)
    290117984   2011-08-08       4         1
    400018758   2009-10-02       5    (NULL)
    400018758   2009-10-02       6         1

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •