How To Delete Duplicate Records From Data Base Expect From One ?

Discussion in 'Programming' started by alexinawhite, Feb 7, 2013.

0
  1. #1
    Hey, My name is sawan and i am learning php now a days. some days before i went for an interview and the interviewer ask me a question that How can we delete duplicate records from data base expect from one? he draw a table in front of me like this..

    1 ----- Jhon
    1 ----- Jhon
    1 ----- Jhon
    1 ----- Jhon
    2 ----- Anny
    2 ----- Anny
    1 ----- Jhon

    so any one give me a mysql query for doing this.

    Thanks
    Sawan
     
    alexinawhite, Feb 7, 2013 IP
  2. gavo

    gavo Active Member

    Messages:
    123
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    70
    #2
    I would create a new table and insert unique names from the old table.
    INSERT INTO new_table (id, name)SELECT id,DISTINCT name FROM old_table

    And set column name to unique to stop any future duplicates.
     
    gavo, Feb 10, 2013 IP
  3. alexinawhite

    alexinawhite Greenhorn

    Messages:
    11
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    It's a good IDEA but it is not a appropriate answer of my question .
     
    alexinawhite, Feb 10, 2013 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,824
    Likes Received:
    4,539
    Best Answers:
    123
    Trophy Points:
    665
    #4
    You sound a bit disgruntled...



    All you have to do is create a ranking table, then delete from the base table where value of the ranking table is greater than 1

    DROP TABLE IF EXISTS `tinyint_asc`;
 
CREATE TABLE `tinyint_asc` (
`value` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (value)
) ;
 
INSERT INTO `tinyint_asc` VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);
    Code (markup):
    and the query
    delete from my table where name in (SELECT  `name`,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      GROUP_CONCAT(`name` ORDER BY mytable DESC),
      ',', value),
    ',', -1)
    AS Name
 
FROM
  mytable, tinyint_asc
WHERE
  tinyint_asc.value > 1
GROUP BY
  `name`);
    Code (markup):
    obviously you'll test the crap out of it before applying to live data but your interviewer will be impressed that you know. I'm guessing you have a heads up on an upcoming interview. Be warned though, they'll expect you to be able to do it quickly and easily if they give you the job. You won't be able to post every question on an online forum and get shitty when people don't answer quickly enough.
     
    sarahk, Feb 10, 2013 IP