Delete duplicate records in SQL

Question

I have this SQL query to select all the duplicated customers who have no orders :

select distinct * from [KUNDEN] k1
where not exists (
select * from [BELEG] b
where k1.Nummer = b.Adressnummer)
and exists (
select * from [KUNDEN] k2
where k1.Nummer <> k2.Nummer
and k1.Name = k2.Name
and k1.Vorname = k2.Vorname)

How can I change this query in order to delete these customers ?

Thanks for your help

Have you tried swapping `select distinct *` with `delete` and see what you got? — D4V1D, Jul 20 '15 at 14:29
http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — Hearner, Jul 20 '15 at 14:30
possible duplicate of [Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — D4V1D, Jul 20 '15 at 14:31
Have a look at this post: http://stackoverflow.com/questions/6353183/how-to-delete-all-duplicate-records-from-sql-table — 6dev6il6, Jul 20 '15 at 14:31

score 0 · Answer 1 · answered Jul 20 '15 at 14:31

0

delete from [KUNDEN]
where nummer in (
    select distinct nummer
    from [KUNDEN] k1
    where not exists (
    select * from [BELEG] b
    where k1.Nummer = b.Adressnummer)
    and exists (
    select * from [KUNDEN] k2
    where k1.Nummer <> k2.Nummer
    and k1.Name = k2.Name
    and k1.Vorname = k2.Vorname)
)

answered Jul 20 '15 at 14:31

Darkwing

314
9
24

Thanks. I have this error : `#1093 - You can't specify target table 'kunden' for update in FROM clause` – bidou88 Jul 21 '15 at 06:46
`delete from [KUNDEN] where nummer in ( select distinct nummer from (select * from [KUNDEN]) k1 where not exists ( select * from [BELEG] b where k1.Nummer = b.Adressnummer) and exists ( select * from [KUNDEN] k2 where k1.Nummer <> k2.Nummer and k1.Name = k2.Name and k1.Vorname = k2.Vorname) )` http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause – Darkwing Jul 21 '15 at 06:54

Delete duplicate records in SQL

1 Answers1