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

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

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