how to delete duplicate entry by one SQL?

Question

i has a table deal:

CREATE TABLE `deal` (
  `id` int(11) NOT NULL auto_increment,
  `site` int(11) NOT NULL default '0',
  `area` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
);

i want to create :

UNIQUE KEY `site` (`site`,`area`,`name`);

but now, name filed is not UNIQUE,when i create this key,i get error:

duplicate entry 'aaa' for key 2

my table datas:

Data
id site  area  name
---------------------------------
1  site1 area1 aaa
2  site1 area2 bbb
3  site1 area1 aaa   <<<< i want delete this
4  site2 area1 ccc
5  site2 area1 ccc   <<<< i want delete this
...

how to do it by one sql string?

thanks for your help :)

Answer 1

Check my answer from this link Hope should help you - Since you already have ID column, ignore the adding of identity in my answer

deleting duplicate records

Answer 2

I think this should do it for you

Delete From deal
Where ID in (

    Select Max(Id)
    From deal
    Group by [site], area, [Name]
    Having Count(id) > 1
        )

As this is a Delete - This is untested so please test it first.

Answer 3

DELETE FROM site
      WHERE id NOT IN (
              SELECT id
                FROM site
            GROUP BY name
            )