I try to use a temporary table to remove duplicates from main table and compose such query:
create temporary table temp_comp
select count(companyname), companyname, companyid from company
group by companyname
order by companyid;
select * from temp_comp;
delete from company c_m where c_m.companyid not in (select t.companyid as cid from temp_comp t);
I get an error message trying this query to execute, it says I make mistake near of the (select t.companyid as cid from temp_comp t) clause. When I run it particularly it works fine. I use mysql version 4.0.16. Is it trouble of subquery using impossibility?
As I said in my first reply, you can't do joins in deletes (unless it's part of a select in a subquery, and as it's not 4.1, that makes it irrelevant).
The linked page above gives a few options as to how you can get around the problem. By far the easiest way is to process the results of what would be your subquery in a scripting language then pass it back to a second query. The third method on that manual page is quite complex, but if you only have access to mysql command line tools, then that may be your only option.