var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: how to delete a duplicate data in a table
delete from table where exists (select lot_no,min(date) from table group by lot_no having count(*) > 1)
is this correct?
but i'm delete all the data in the table?
If you run this:
Are the returned results what you want to delete? I don't think the min(date) bit is what you want.
select lot_no,min(date) from table group by lot_no having count(*) > 1)
yes this is the result that i wanted to delete.
because i have two records i want to keep the latest date so that is why i wanted it to be delete and i will use it for another complex query so that i why i don't want it to my table.
delete from table where (lot_no,date) in (select lot_no,min(date) from table group by lot_no having count(*) > 1);
- if both rows have the same date, both will be deleted
- if there are more than two rows with same lot_no, only that with the oldest date will be deleted
<edit> maybe this is a better solution:
delete from table a where exists ( select * from table b where a.lot_no = b.lot_no and a.date < b.date )
Last edited by umayer; 06-13-07 at 08:05.