SQL question - HELP

[kschuster]

What is the syntax to delete records based on a select??

This sql statement the retreives the rows for deletion is

select from m_list a, m_list b where a.m_address=b.m_address AND
a.m_name=b.m_name AND a.id!=b.id
group by a.m_address having count()>1
order by a.m_address

I'm trying to find duplicate and delete them

Thanks
Keith

[kschuster]

This is a mysql db by the way

[Sxooter]

The easiest way is to just select the data into a temp table with a distinct, then just put it back in.

select distinct into newtable from oldtable;
delete from oldtable;
insert into oldtable (select from newtable);

That last line might have a unique mysql syntax, but it might support that kind of "subselect". ( in this case, the subselect is more of a language construct than a true subselect, so that's why I think it might well already be implemented in mysql, it's probably an easy case to program.)