delete dupes with a unique id using a temp table

On 2011-01-18 18:09, jr wrote:
[...]
>> This query is illegal and will give you random results. It will only
>> work if id is functionally dependent of bu, ndc. You can prevent this
>> misbehaviour by enabling ONLY_FULL_GROUP_BY in sql_mode, mysql will then
>> return an error instead of a wrong result.
>>
>> /Lennart
>>
>> [...]
>
> thanks for explaining it. It was very useful.

You can verify by:

set sql_mode='';

create table t (
id int not null primary key,
bu int not null,
ndc int not null
) engine = innodb;

insert into t (id, bu, ndc) values (1,1,1),(2,1,1),(3,1,1);

select id, bu, ndc, count(1) from t group by bu, ndc;
+----+----+-----+----------+
| id | bu | ndc | count(1) |
+----+----+-----+----------+
| 1 | 1 | 1 | 3 |
+----+----+-----+----------+
1 row in set (0.00 sec)

id might be any one of 1, 2 or 3

/Lennart