467,146 Members | 1,008 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

Deleting duplicates

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
Mar 6 '07 #1
  • viewed: 1685
Share:
6 Replies
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
Mar 6 '07 #2
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.
Mar 6 '07 #3
8TB
Changed thread title.
Mar 6 '07 #4
ronverdonk
Expert 4TB
Easiest way to do this is via a temporary table. Like the following code shows

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE temp LIKE sample_table;
  2. INSERT INTO temp SELECT sample_table.* FROM sample_table;
  3. DELETE FROM sample_table 
  4.    WHERE id NOT IN (SELECT MIN(id) FROM temp GROUP BY name);
  5. DROP TABLE temp; 
Ronald :cool:
Mar 6 '07 #5
Is any other simple method to delete the duplicate records from the table?


Thanks & Regards,
S.Ashokkumar
Mar 7 '07 #6
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
  1. create temporary table sample_table1(id int,name varchar(20));   
  2. insert into sample_table1(id,name) select DISTINCT id,name from 
  3. sample_table;   
  4. delete from sample_table;
  5. insert into sample_table(id,name) select id,name from sample_table1;
Regards,
Sang
Mar 7 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Jerry Barnes | last post: by
18 posts views Thread by Dan | last post: by
1 post views Thread by Brian Keanie | last post: by
19 posts views Thread by MaXX | last post: by
3 posts views Thread by Maury | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.