CODEWITHSUNDEEP
mysqlsql
Maulik patel
Maulik patel

Reputation: 1601

Removing duplicate rows from a table

id     lat                long     speed      date          address
 1    22.92138131   72.44103313     3.96 km/h     2011-09-26  National, Gujarat, India
 2    22.92138145   72.44103413     13.96 km/h     2011-09-26  National, Gujarat, India
 3    22.92138134   72.44103423     15.96 km/h     2011-09-26  National, Gujarat, India
 4    22.92138454   72.44103233     13.96 km/h     2011-09-26  10t ring Rd, Nehru Nagar
 5    22.92138354   72.44102533     13.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad
 6    22.92138484   72.44103293     19.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad

I want to write a query such that my result looks like this:

id     lat                long     speed      date          address
 1    22.92138131   72.44103313     3.96 km/h     2011-09-26  National, Gujarat, India
 4    22.92138454   72.44103233     13.96 km/h     2011-09-26  10t ring Rd, Nehru Nagar
 5    22.92138354   72.44102533     13.96 km/h     2011-09-26  Anandnagar Rd, Ahmedabad

I want to remove duplicate rows according to the address.

Upvotes: 8

Views: 14009

Answers (6)

Nimit Dudani
Nimit Dudani

Reputation: 4860

Need to create duplicate/temporary table with same field what your current table have.

Then execute below SQL

First clear temporary table :

DELETE FROM `#TMP_TABLE#`;

Insert record in temporary table as per your expectation.

INSERT INTO `#TMP_TABLE#`
SELECT T . *
FROM #TABLE# T
INNER JOIN (
    SELECT MIN( ID ) AS ID
    FROM #TABLE#
    GROUP BY address
    ) SUB ON T.id = SUB.id

Truncate main table

DELETE FROM `#TABLE#`;

Copy Data from temporary table

INSERT INTO #TABLE# SELECT * FROM  `#TMP_TABLE#`

Upvotes: 2

barryhunter
barryhunter

Reputation: 21081

If you don't care which of the rows you keep

ALTER IGNORE TABLE table ADD UNIQUE KEY 'address' (`address`);

The 'IGNORE' is important, that means to silently ignore duplicate data. (ie ignores it when inserting into the 'new version' of the table.)

May want to remove the index afterwoods

ALTER TABLE table DROP KEY 'address';

Upvotes: 4

Ian Nelson
Ian Nelson

Reputation: 58783

Assuming that you wish to return the rows with the smallest ID values:

SELECT 
    *
FROM
TABLENAME T INNER JOIN
(
    SELECT MIN(ID) AS ID FROM TableName 
    GROUP BY Address
) SUB ON T.ID = SUB.ID

Upvotes: 2

DavidEG
DavidEG

Reputation: 5957

To check what you are going to delete:

SELECT distinct t1.*
  FROM yourtable as t1
  join yourtable as t2
 WHERE t1.address = t2.address
   and t1.id < t2.id

If you are happy with that:

DELETE t1
  FROM yourtable as t1
  join yourtable as t2
 WHERE t1.address = t2.address
   and t1.id < t2.id

This way you keep the record with the max value on id column

Upvotes: 9

user769394
user769394

Reputation:

You Can Do this Easily by Doing these 3 easy steps and therefore 3 SQL statements:

Step 1: Move the non duplicates (unique tuples) into a temporary table CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];

Step 2: delete delete the old table We no longer need the table with all the duplicate entries, so drop it! DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table RENAME TABLE new_table TO old_table;

For Another Way You Can go to the Bellow Link... Its is Also a good Way... Difficult but with less statements http://dev.mysql.com/doc/refman/5.0/en/insert.html

Upvotes: 0

pratik garg
pratik garg

Reputation: 3342

delete from table_name tb where id not in 
   (select min(id) from table_name tb1 group by address)

I assumed that you want to remove rows having address duplicacy and want to keep minimum id's row in your table

Upvotes: 0

Related Questions