Q: How do I drop duplicate data from a table?

A: Assume there is a student table: students(id char(5),name char(20)); To drop duplicate data from this table:

1. Use select to move the duplicate id to a temporary table, 'temptable':

   dmSQL> select id 
   2> from student 
   3> group by id having count(*)>1 
   4> into temptable;
   		  

2. Then drop all the duplicate id data

   dmSQL> delete from student
   2> where id in ( 
   3> select id from temptable 
   4> ); 
   			  

Caution:

1. If there are two records including id=3008,these two records will be deleted.
2. You can build an index in the id and name columns, which will speed up execution time