Remove duplication of combination 2 columns-CodePudding

I want to remove all duplicates where combination of first name and last name is same

table users mysql> select * from users;

 ---- ------------ ----------- 
| id | LastName   | FirstName |
 ---- ------------ ----------- 
|  1 | Kowalski   | Jan       |
|  2 | Malinowski | Marian    |
|  3 | Malinowski | Marian    |
|  4 | Kowalski   | Jan       |
|  5 | Malinowski | Marian    |
|  6 | Malinowski | Marian    |
 ---- ------------ -----------

I've created script

set @x = 1;
set @previous_name = '';


DELETE FROM users where id IN (SELECT id from (
    select id, @previous_name,IF (CONCAT(FirstName, LastName) = @previous_name, @x:= @x   1, IF(@previous_name:=CONCAT(FirstName, LastName), @x, IF(@x:=1, @x, @x))) as occurance
        from users order by CONCAT(FirstName, LastName)
    ) AS occurance_table where occurance_table.occurance > 1);

but sql returns error

ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'JanKowalski'

I found a few similar questions, but solution were remove and word form syntax.

I want to prepare db for adding unique constrain for 2 columns, so I want to clear table from duplications.

What is best way to reach it?

CodePudding user response：

There is no need for a script. A single query is enough:

delete from users
where id not in
(
  select min(id)
  from users
  group by LastName, FirstName
)

The subselect gets the lowest user id for each unique set of name. The outer delete query deletes everything but that.

CodePudding user response：

I tried with the query mentioned in Answer section. I believe that does not work. Instead I have modified the query to work

DELETE FROM users
WHERE id NOT IN
(
  SELECT MIN(a.id)
  FROM (SELECT * FROM users) a
  GROUP BY a.LastName, a.FirstName
  )

Please do correct me if I am wrong. @juergen