Deleting duplicates

write2ashokkumar

hi...

i have the table values with duplicate records, i want to delete the duplicate records, How to delete the duplicate records? I need the query to delete the duplicate rows

Example:

id ---- name
1 ---- xxxx
2 ---- yyyy
1 ---- xxxx
3 ---- zzzz
3 ---- zzzz

i want to delete the duplicate rows from the above table. Table name is sample_table.

Thanks
S.Ashokkumar

sang

hi...

i have the table values with duplicate records, i want to delete the duplicate records, How to delete the duplicate records? I need the query to delete the duplicate rows

Example:

id ---- name
1 ---- xxxx
2 ---- yyyy
1 ---- xxxx
3 ---- zzzz
3 ---- zzzz

i want to delete the duplicate rows from the above table. Table name is sample_table.

Thanks
S.Ashokkumar

Hi Ashok
The DuplicateRecords are deleted by using this Query
DELETE FROM table_name WHERE id=1 LIMIT 1; same as id3.

To avoid the duplicate records set primary key to your id field.

I think it is useful to you

Thanks
Sang

write2ashokkumar

Hi Ashok
The DuplicateRecords are deleted by using this Query
DELETE FROM table_name WHERE id=1 LIMIT 1; same as id3.

To avoid the duplicate records set primary key to your id field.

I think it is useful to you

Thanks
Sang

hi...

Thanks for ur reply...
i know to delete the row based on values.... consider one very big table... that table have duplicate record.... Then how can we delete the duplicare rows without specified values.

Thanks
S.Ashokkumar.

r035198x

Changed thread title.

ronverdonk

Easiest way to do this is via a temporary table. Like the following code shows

Expand|Select|Wrap|Line Numbers

 CREATE TABLE temp LIKE sample_table;

INSERT INTO temp SELECT sample_table.* FROM sample_table;

DELETE FROM sample_table 

   WHERE id NOT IN (SELECT MIN(id) FROM temp GROUP BY name);

DROP TABLE temp;

Ronald :cool:

write2ashokkumar

Is any other simple method to delete the duplicate records from the table?

Thanks & Regards,
S.Ashokkumar

sang

Is any other simple method to delete the duplicate records from the table?

Thanks & Regards,
S.Ashokkumar

Another method to do

Expand|Select|Wrap|Line Numbers

 create temporary table sample_table1(id int,name varchar(20));   

insert into sample_table1(id,name) select DISTINCT id,name from 

sample_table;   

delete from sample_table;

insert into sample_table(id,name) select id,name from sample_table1;

Regards,
Sang

Deleting duplicates

Post your reply

Similar topics