Delete duplicate rows in a table

Question

I used the following SELECT to find the repeated values:

SELECT nome, Count(nome) FROM projeto
GROUP BY nome
HAVING Count(nome)>1

Now I need to delete everything that is repeated and leave only the first that was inserted. I am trying to do it this way:

DELETE FROM projeto
WHERE nome IN
  (SELECT nome FROM projeto
   GROUP BY nome
   HAVING Count(nome)>1)
AND NOT id_projeto IN
  (SELECT Min(id_projeto) FROM projeto
   GROUP BY nome
   HAVING Count(nome)>1)

However, I am getting the following error: #1093 - You can’t specify target table 'project' for update in FROM clause

Any idea what might be wrong?

Answer 1

There’s some way to do this, what comes to mind in a simple way is something like:

-- Obter apenas um ocorrencia de nome e guardar na temporaria.
select distinct nome into #tmp From projeto

-- Apagar dados originais.
Delete from projeto

-- Voltar a repor os dados na tabela original.
Insert into projeto select * from #tmp