How to eliminate 'duplicates' in my database

Discussion in 'Databases' started by pmf123, Nov 19, 2013.

0
  1. #1
    I have a database with some lines duplicated as follows:

    MRSDxxx xxx 29/1x/19xx x, CHURCH ROAD,OARE, FAVERSHAM,,ME13 0QA
    MRSDxxx xxx 29/1x/19xx x CHURCH ROAD, OARE, FAVERSHAM,,, ME13 0QA

    as you can see, the only different is the number and name of street are in separate columns in some records and all in one in others... everything else is identical

    trying to figure out a query to find these
     
    pmf123, Nov 19, 2013 IP
  2. khodem

    khodem Well-Known Member

    Messages:
    206
    Likes Received:
    8
    Best Answers:
    3
    Trophy Points:
    120
    #2
    Hi, I have created a table and added duplicate record then against that I have created query please change the fields name according to your need, here is the query and link to solution, just change the * to T

    h**p://www.sqlfiddle.com/#!2/481e9/3

    select ip,logs,count(ip) as countofrecord from serversip
group by ip
    Code (markup):

    cheers
     
    khodem, Dec 8, 2013 IP
  3. pearllike

    pearllike Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    Hey,
    Though both the rows contain identical information but have different column values so if you compare them by column values the problem will not be solved.
     
    pearllike, Dec 11, 2013 IP
  4. junedc

    junedc Active Member

    Messages:
    175
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #4
    as per SQL is concern this is not duplicate. even a group by will not find these as duplicate count.

    what i suggest you remove the " " and "," and replace it with empty strings "". Then do the comparison
     
    junedc, Dec 13, 2013 IP
  5. khodem

    khodem Well-Known Member

    Messages:
    206
    Likes Received:
    8
    Best Answers:
    3
    Trophy Points:
    120
    #5
    please read question he asked to find them not to delete them... group by will find a record or column name that happens more than one time so when you count that it will show number records with similar data... please check that fiddler
     
    khodem, Dec 13, 2013 IP
  6. junedc

    junedc Active Member

    Messages:
    175
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #6
    did i mention "Delete" in my post? read my answer very well.

    MRSDxxx xxx 29/1x/19xx x, CHURCH ROAD,OARE, FAVERSHAM,,ME13 0QA
    MRSDxxx xxx 29/1x/19xx x CHURCH ROAD, OARE, FAVERSHAM,,, ME13 0QA

    Based on his data, everything is the same , so i suggest concat all fields , then remove comma and space. then do the comparison.
     
    junedc, Dec 13, 2013 IP
  7. evtimii

    evtimii Active Member

    Messages:
    55
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    70
    #7
    If this is in one column this should work:

    
SELECT details
FROM something group by REPLACE(REPLACE(details,"," , "")," ", "" )
    Code (markup):
    http://sqlfiddle.com/#!2/81276/2


    If the data is spread into multiple columns, this should work:

    SELECT details,details2
FROM something group by
REPLACE(REPLACE(CONCAT(details,'',IFNULL(details2,'' ) ),"," , "")," ", "" )
    Code (markup):
    http://sqlfiddle.com/#!2/db5f09/1
     
    evtimii, Dec 13, 2013 IP