Feb 02, 2022, updated at 13:51:32 (UTC)

5216

Doppelte Einträge in SQL Tabelle löschen - ein Eintrag soll stehen bleiben

Hallo,
ich habe eine Tabelle wo mehrfach Einträge sind die die identische Emailadresse haben. Ich brauche nun einen SQL Befehl der alle doppelten Einträge löscht und nur einen davon stehen lässt. Also z.B. wenn 3 mal die gleiche Emailadresse vorhanden ist zweimal löschen und eine stehen lassen. Es ist dabei egal welcher von den Einträgen. Ob nun der erste oder letzte oder ein anderer. Hauptsache einer bleibt stehen. Die Tabelle hat nur knapp 2400 Einträge.

Die Tabelle sieht so aus:

Habe im Internet ein paar SQL Abfragen ausprobiert, aber immer ein paar Fehler bekommen wohl weil die Tabelle kein ID Feld hat.
Leider kann ich die Tabelle nicht verändern, sie muss so bleiben.
Die Felder email und passw... sind jeweils varchar Felder.

Habt Ihr einen Tipp für mich was ich angeben muss ?
Bin leider im Bereich SQL noch im Anfängerstadium.

LG

Please also mark the comments that contributed to the solution of the article

Content-Key: 1812832642

Url: https://administrator.de/contentid/1812832642

Printed on: May 7, 2024 at 22:05 o'clock

16 Comments

Latest comment

works for me:

edb=*# select * from commodore ;
 name 
------
 bla
 bla
 foo
 bla
 foo
 foo
 eins
 zwei
 zwei
(9 rows)
edb=*# with foo as (select name, max(ctid) as ctid from commodore group by name ) delete from commodore where ctid not in (select ctid from foo);
DELETE 5
edb=*# select * from commodore ;
 name 
------
 bla
 foo
 eins
 zwei
(4 rows)

edb=*# 

@akretschmer s Lösung setzt eine ID voraus, die ja nicht gegen ist.

Um welches SQL geht es?

Kannst du eine neue Tabelle anlegen und die Datensätze übergangsweise alle löschen oder gibt es Abhängigkeiten?

Zitat von @ukulele-7:

@akretschmer s Lösung setzt eine ID voraus, die ja nicht gegen ist.

nicht ganz korrekt, die Tabelle hatte nur eine Spalte:

edb=*# \d commodore 
            Table "public.commodore"  
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 name   | text |           |          | 

edb=*#

siehe auch die Ausgabe von select * im Post ...

Zitat von @CommodoreC64:

ich habe eine Tabelle wo mehrfach Einträge sind die die identische Emailadresse haben. Ich brauche nun einen SQL Befehl der alle doppelten Einträge löscht und nur einen davon stehen lässt.

Benutze DISTINCT.

Die Tabelle muss ich unter dem Microsoft SQL Server Management Studio bearbeiten bzw. darunter bearbeite ich sie.

Sorry aber irgendwie scheint es teilweise um eine andere Tabelle zu gehen.
Meine hat nur die Felder email, passw, central, creator und einen select Befehl habe ich nicht angegeben.

Zitat von @akretschmer:

Zitat von @ukulele-7:

@akretschmer s Lösung setzt eine ID voraus, die ja nicht gegen ist.

nicht ganz korrekt, die Tabelle hatte nur eine Spalte:

edb=*# \d commodore 
            Table "public.commodore"  
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 name   | text |           |          | 

edb=*#

siehe auch die Ausgabe von select * im Post ...

... und wenn man mein Beispiel sich genau anschaut, findet man da sicherlich sogar einen Hinweis für den Fragesteller

Na gut, ich will mal nicht so sein, hier Ideen, wie man es machen kann:

Nutzung einer 'hidden' Spalte, hier in PostgreSQL die ctid-Spalte
Aggregation auf z.B. max(password) gruppiert nach email
Nutzung von Window-Funktion row_number() over (partition by email) und dann alle löschen, und nur die behalten, wo row_number = 1 ist (behalten aller Rows mit dem Passwort wo row_number = 1 ist)

Wenn man mein in #1 gezeigtes Beispiel nimmt sollte das nun als Übungsaufgabe machbar sein.

Und für die Zukunft: jede Tabelle mit Primary Key anlegen.

ctid ist halt PostgreSQL spezifisch, gibt es nicht bei MSSQL
Auch MSSQL hat Hidden Columns, aber die können nicht abgefragt oder genutzt werden (zumindest ist das mein letzter Stand), siehe http://rusanu.com/2011/10/20/sql-server-table-columns-under-the-hood/
Aggregation wird nur mit einer 2ten Tabelle klappen in der zwischengespeichert wird denn DELETE GROUP BY gibts nicht.
ROW_NUMBER() geht ebenso nicht, ich kann es nicht im WHERE Teil verwenden weil es da noch nicht existiert und ich kann nichts mit einer ROW_NUMBER() machen wenn DELETE keinen Zugriff darauf hat.

Aber ja, es gibt eine Lösung:
https://www.mssqltips.com/sqlservertip/1103/delete-duplicate-rows-with-n ...
Und ja, ohne Primary Key ist irgendwie Pfusch.

Zitat von @ukulele-7:

* Aggregation wird nur mit einer 2ten Tabelle klappen in der zwischengespeichert wird denn DELETE GROUP BY gibts nicht.

naja, ich dachte an sowas wie folgt. Fragesteller hat ja eine weitere Spalte mit Passwort, welches (hoffentlich) relativ unique sein sollte (bei sicheren Passwörtern ...), daher erweitere ich meine Tabelle mal...

edb=*# alter table commodore add column password text;
ALTER TABLE
edb=*# update commodore set password = random()::text;
UPDATE 9
edb=*# select * from commodore ;
 name |      password       
------+---------------------
 bla  | 0.07895443615100817
 bla  | 0.36858223721896977
 foo  | 0.9892239391797446
 bla  | 0.8135119986958621
 foo  | 0.4427922607156205
 foo  | 0.13558457805552493
 eins | 0.297047779289322
 zwei | 0.5384773433722536
 zwei | 0.6127916409348586
(9 rows)

edb=*# with foo as (select name, max(password) as pw from commodore group by name) delete from commodore where password not in (select pw from foo);
DELETE 5
edb=*# select * from commodore ;
 name |      password      
------+--------------------
 foo  | 0.9892239391797446
 bla  | 0.8135119986958621
 eins | 0.297047779289322
 zwei | 0.6127916409348586
(4 rows)

Aber möglicherweise kann M$SQL keine wCTE (writeable Common Table Expressions).

Moin,

so auf die Schnelle:
Baue ein Select mit

SELECT email, em_pwd = MAX(CONCAT(email, password))
FROM table
GROUP BY email

Damit hättest du quasi exakt für eine email-Adresse einen einzigen EIntrag.
Dieses Statement in eine cte verpackt, kannst du dann zum löschen nutzen:

WITH cte_ep as (
 SELECT email, em_pwd = MAX(CONCAT(email, password))
 FROM table
 GROUP BY email
)

SELECT
 table.*
FROM table
INNER JOIN cte_ep on 
  CONCAT(table.email,table.password) <> cte_ep.em_pwd
  AND table.email = cte_mp.email

Statt dem SELECT dann ein passendes DELETE und es müsste laufen...

Ist aber absolut ungetestet

Gruß
em-pie

Doch das müsste gehen aber ich sehe allein schon 2 PWs mit identischem Anfang, wenn auch unterschiedlichen Benutzernamen davor. Ich würde mich nicht auf die PW-Spalte verlassen, nicht ohne jeden Datensatz vorher abzugleichen.

PS: Ich weise nochmal auf die absolut simple Lösung hin:
https://www.mssqltips.com/sqlservertip/1103/delete-duplicate-rows-with-n ...

PPS: Dort wird sogar eine CTE Lösung genannt, hätte ich nicht gedacht das das geht...

Moin,

ich glaube, Ihr habt ihn verschreckt mit Eurer Fachsimpelei, er ist so still ...

@c64: versuch mal

with tmp as (select row_number () over (partition by email order by email) as Zeile from Tabelle)
delete from tmp where Zeile <> 1

Mußt nur den richtigen Tabellennamen verwenden, dann sollte es gehen.

Gruß, Mad Max

Zitat von @MadMax:

Moin,

ich glaube, Ihr habt ihn verschreckt mit Eurer Fachsimpelei, er ist so still ...

@c64: versuch mal

with tmp as (select row_number () over (partition by email order by email) as Zeile from Tabelle)
delete from tmp where Zeile <> 1

Mußt nur den richtigen Tabellennamen verwenden, dann sollte es gehen.

Gruß, Mad Max

das dumme ist nur, daß wir aus 'Tabelle' löschen wollen, nicht aus der CTE-Tabelle 'tmp' ...

Kein Problem, die CTE-Tabelle tmp gibt es ja nicht wirklich, das wird durchgereicht in die Tabelle.

Ich hab es ausprobiert, das funktioniert, zumindest unter SQL Server 2017

Das geht auch einfacher. Ersetze Commodore durch den Tabellennamen:

Subquery gruppiert die Einträge nach der Email und sortiert nach Password. Es wird eine Spalte Sort erzeugt die bei jeder neuen Email hochzählt beginnend mit 1.

Wenn du ein Delete rundherum machst, dass alle Einträge mit Sort >1 löscht, sind die Dubletten weg.
Wenn du es testen willst, mache aus Delete X ein Select *. Das Ergebnis des Select sind alle Dubletten, die rausgeworfen werden.

DELETE x
FROM
(
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY Email
           ORDER BY Password ASC) AS Sort
    FROM Commodore
) x
WHERE x.Sort > 1;

Gruß
Grinskeks

Sorry für meine späte Rückmeldung.
War einiges los auf der Arbeit so dass ich erst einmal an anderen Themen arbeiten musste.

In der Tat war ich bei Euren Angaben teilweise verwirrt als SQL Novize 😵

Aber der letzte Beitrag von @Grinskeks hat mir genau das gebracht was ich wollte.
Tatsächlich habe ich einmal erst mit Select getestet (der Tipp zusätzlich war beruhigend 😃).
So konnte ich sehen das alles richtig war.
Danach mit delete, Bingo alles wie gewünscht.
Danke an Alle.

LG

German solved Question Databases Development

Hotly discussed

Wireguard with systemd and nftablesLinuxero - 9 Comments