Datenbank Duplicates

[-187-]

Ich hab nochmal ne Frage zur Datenbank. Und zwar füge ich hin und wieder neue Einträge hinzu. Da ich keine Duplicates haben möchte habe ich auf das betroffene Feld folgendes gemacht:

Delphi-Quellcode:

IBTable1.AddIndex('UNIQUE', 'NAMES', [ixUnique, ixCaseInsensitive]);

Jetzt wollte ich die Einträge mal überprüfen und habe das Feld exportiert und in einem separatem Tool auf Duplicates geprüft. Komischer weise fehlen nach der Überprüfung 60 Einträge. Gesamt waren es rund 9000.

So überprüfe ich die Duplicates:

Delphi-Quellcode:

procedure TForm1.Button1Click(Sender: TObject);
var
  sl: TStringList;
begin
  sl:=TStringList.Create;
  sl.Duplicates:=dupIgnore;
  sl.Sorted:=True;
  sl.Text:=Memo1.Text;
  Memo1.Text:=sl.Text;
end;

[idefix2]

????

Wo fehlen Einträge? in der Datenbank? Vom Exportieren einer Spalte können keine Datensätze verschwinden.
Hast Du noch eine Kopie der DB, in der die Sätze da sind?

Wenn Du glaubst, dass es Duplicates gibt, kannst Du das doch direkt in der Datenbank mit einem einfachen SQL Statement feststellen, wozu ein externes Tool?

[-187-]

Nee nee ich exportiere 9000 Records und lasse die dann durch das Tool laufen. Hinterher habe ich dann noch 8940.

Ist doch egal wie ich es prüfe, für mich war es so die einfachste Variante. Es geht einfach darum das sich Duplicates in der DB befinden und ich mir das nicht erklären kann ? !

Wir würdest du es überprüfen ?

[haentschman]

sicher, daß der Export wirklich 9000 sind ?

[-187-]

Ja ich hab jetzt gerade zahlen genommen. In Wirklichkeit sind es 9366 nach dem Export und 9305 nach dem duplicate remove.

[idefix2]

Wenn feld das zu prüfende Feld in der Tabelle tabelle ist, z.B. so:

SQL-Code:

select feld from tabelle 
group by feld having (count(*)>1)

Dann bekommst Du auch gleich serviert, welche Feldwerte mehrfach vorkommen.

[-187-]

Hm okay das Query sagt mir das es keine Duplicates gibt. Seltsam ! Ich schreib mein kleines Tool mal um damit ich dir die vermeintlichen Duplicates ausgeben kann.

[idefix2]

Was sein könnte, ist, dass Dein Tool vielleicht Daten als Duplicates ansieht, die für Firebird unterschiedlich sind, das hängt bei Firebird vom verwendeten Zeichensatz und von der verwendeten Collation ab (Gross-Kleinschreibung, Umlaute etc.)

Versuch einmal

SQL-Code:

select feld from tabelle
group by upper(feld) having (count(*)>1)

[Namenloser]

Enthalten manche der Feldinhalte vielleicht Zeilenumbrüche?

[-187-]

@idefix, dann bekomm ich "Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)"

@NamenLozer, also ich habe in meiner exportierten list genauso viele zeile wie rows in der db oder worauf möchtest du hinaus ?

[idefix2]

Ja, Du hast recht, so wie oben geschrieben geht es nicht. aber so:

SQL-Code:

select upper(ip_name) from musik
group by upper(ip_name) having count(*)>1

Das hab ich in meiner DB probiert, ip_name -> feld, musik -> tabelle

[-187-]

Hm wie es scheint bist du dem Problem auf die Schliche gekommen. Da ist die genau die Anzahl an Duplicates. Meine SQL Kenntnisse sind minimal. Was genau machen wir jetzt anders ? Wir selektieren alle UPPER CASE records die es öfter als einmal gibt. Wieso wurden die vorher nicht mit überprüft ? Oder macht das DBMS einen unterschied zwischen AAA und aaa ?

[mkinzler]

Natürlich besteht ein Unterschied zwischen 'AAA', 'aaa' und 'Aaa' usw. Willst das nicht musst du einen Expression-Index auf UPPER() anlegen ( geht ab FB 2.1)

[borwin]

Bei der Abfrage wird der Inhalt des Feldes genau überprüft. Dabei erflogt die Überprüfung Zeichenweise und ein 'A' ist dann etwas anderes als ein 'a'. Um das zu umgehen werden alle Zeichen auf UPPER gesetzt. Eine weitere Falle beim abfragen von Texten sind die Leerzeichen. Für die Datenbank ist 'MEIER' und 'MEIER ' nicht gleich. Um auch diesen Fall zu umgehen noch ein TRIM um die Felder legen.
Dann sieht das ganze dann so aus

Code:

SELECT TRIM(UPPER(ip_name)) 
FROM musik
GROUP BY TRIM(UPPER(ip_name)) 
HAVING COUNT(*)>1

Um das Einfügen von doppelten Datensätzen zu verhindern musst Du Deinen Index verändern.
Lege ein sogenantes COMPUTER BY Feld an mit Index an

Zitat:

create index idx_name on musik computed by (trim(upper(ip_name)));

Das verhinder schon doppelte Datensätze. Geht ab FB 2.0

Gruß Borwin

[-187-]

Hm borwin also doppelte Einträge bekomme ich nicht dank dieser Zeile:

Delphi-Quellcode:

IBTable1.AddIndex('UNIQUE', 'NAMES', [ixUnique, ixCaseInsensitive]);

Ich glaube ich werde die strings bevor ich zu der DB hinzufüge einfach einmal vorbehandeln:

Delphi-Quellcode:

MyStr:= LowerCase(MyStr);

Damit sollte das Problem endlich gelöst sein, Danke ! :)

[idefix2]

Zitat:

Natürlich besteht ein Unterschied zwischen 'AAA', 'aaa' und 'Aaa' usw.

So natürlich ist es nicht. Wenn eine Collating Sequence für die Spalte angegeben ist, der Case-insensitiv ist, behandelt Firebird nach dem, was ich bis jetzt gelesen hat, unterschiedliche Case-Werte auch im Vergleich als identisch.

Die neusten Versionen von Firebird erlauben eigene Collating Sequences zu erstellen, wenn Du willst, dass Gross und Kleinbuchstaben in dem Feld als gleich behandelt werden, müsstest Du eine entsprechende Collating Sequence erstellen und bei dem Feld angeben.

[mkinzler]

Da ist aber der Weg über den Expression Index einfacher

[-187-]

Angenommen ich füge diesen Index im nachhinein ein, werden dann die (jetzt neuen) Duplicates rausgeschmissen oder wie verhält sich das?

[mkinzler]

Nein, werden sie wohl nicht. was aber geanu passiert, kann ich dir nicht sagen.

[idefix2]

Zitat:

Da ist aber der Weg über den Expression Index einfacher

Es kommt darauf an. Bei einer kleinen Anwendung mit nur einer derartigen Spalte und einer Abfrage ist das etwas einfacher - wobei ja eine case-insensitive Collation in einem einzeiler von einer case-sensitiven abgeleitet werden kann, also auch nicht sehr kompliziert ist.

Bei einer grösseren Anwendung, wenn man viele Datenbankfelder standardmässig case insentiv haben will, sollte man aber auf jeden Fall besser eine collation erstellen. Sonst muss man bei jeder SQL-Abfrage auf Gleichheit an das Uppercase denken. Prinzipiell sollte man beim Design einer Anwendung eher darauf schauen, möglichst viel Logik in die Datenbank-DLL auszulagern (Trigger, Collations, Calculated Fields, Views). Dann ist die Programmierung der Anwendungen nachher schneller und weniger fehleranfällig.

[mkinzler]

Bei einem entsprechenden Index sollte das Upper() in der Abfrage nicht notwendig sein

[idefix2]

Zitat:

Angenommen ich füge diesen Index im nachhinein ein, werden dann die (jetzt neuen) Duplicates rausgeschmissen oder wie verhält sich das?

Ich würde eine neue Tabelle mit geeigneter Collation erstellen und die Werte der alten Tabelle mittels "Update or insert" Statement in die neue Tabelle einfügen, dann die alte Tabelle löschen und die neue Tabelle umbenennen. Dann sind die Duplicates weg.

[idefix2]

Zitat:

Bei einem entsprechenden Index sollte das Upper() in der Abfrage nicht notwendig sein

Das glaube ich nicht.
Ein Index wird von Firebird ja nur dazu verwendet, Abfragen zu beschleunigen, die zu dem Index passen. Bei der Abfrage selbst gibst Du aber keinen Index an, die Abfrage ist also erst einmal Indexunabhängig defniert. Kommt in der Abfrage kein UPPER vor und Du hast einen Index mit UPPER definiert, dann wird dieser Index für die Abfrage ganz einfach nicht verwendet, weil er ja nicht zur Abfrage passt.

[-187-]

Hm nachdem ich die Duplicates jetzt gefunden habe müsste ich diese noch löschen :)

Code:

SELECT UPPER(VORNAME) FROM NAMEN1 GROUP BY UPPER(VORNAME) HAVING (COUNT(*)>1)

Mein Anlauf war das SELECT Statement durch das DELETE Statement zu ersetzen aber dann bekomm ich die Fehlermeldung "Unknown token Upper".

Hat jemand einen Ansatz wie man duplicates aus einer DB löscht ?

[mkinzler]

SQL-Code:

delete from namen1 where id not in ( SELECT MIN(ID), UPPER(VORNAME) FROM NAMEN1 GROUP BY UPPER(VORNAME));

[DeddyH]

Nur so ein Gedanke (ID soll der PK sein):

SQL-Code:

DELETE FROM NAMEN1
WHERE ID IN (
  SELECT ID 
  FROM NAMEN1 
  GROUP BY UPPER(VORNAME) 
  HAVING (COUNT(*)>1)
)

[edit] Das von Markus ist besser, meins würde ja alle Datensätze löschen, die doppelt vorgekommen sind. [/edit]

[-187-]

Hm ich habe kein Feld "id" ... Ist das zwingend notwendig ?

Reicht es nicht wenn ich das zu bearbeitende Feld betrachte ?

[mkinzler]

Nein, denn du brauchst ja ein Kriterium, um die datensätze zu unterscheiden

[-187-]

Hm Ok also brauche ich jetzt ein zusätzliches Feld mit einem Index Typ, Richtig?

Code:

ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);

Wie sieht so ein Index dann aus? Ist das eine vorlaufende Nummer von 0,1,2,3 .. usw?

[mkinzler]

Eine Tabelle sollte immer einen PK haben ( am Besten einen syntetischen; ist aber Geschmacksache).
In deinem Fall würde ich eine neue Tabelle erzeugen ( mit ID mit autoinc) und dann die Werte von der "alten" in die neue Kopieren:

SQL-Code:

Insert into Name ( Vorname) select UPPER(VORNAME) FROM NAMEN1 GROUP BY UPPER(VORNAME);

[-187-]

Ich dachte ich brauche einfach nur ein zusätzliches Feld "ID" welches einen Index Wert hat ?

Wieso muss ich denn mein Feld "Vornamen" jetzt einen Index haben ?

[fkerber]

Davon steht doch nirgendwo was?
Es ging Markus doch nur ums Kopieren in eine neue Tabelle...


Liebe Grüße,
Frederic

[mkinzler]

Zitat:

Davon steht doch nirgendwo was?

In meinem Beitrag jedenfalls nicht. Durch das Kopieren in eine neue Tabelle kannst du den Datensätzen einfach einen künstlichen Primärschlüssel verpassen und gleichzeitig die doppelten Einträge eliminieren.

[-187-]

Achso ja ich hatte mich verlesen. Kann ich nicht in meine aktuelle Tabelle einen Primärschlüssel nachträglich einbauen?

[mkinzler]

Du kannst jedem Eintrag manuell einen Vergeben. Das Kopieren in eine neue Tabelle ercsheint mir aber der bessere Weg ( da so auch noch dein primäres Problem der Dupletten gelöst wird :wink:)

[-187-]

Wie kann es denn eigentlich sein das da Duplicates drinne sind irgendwie kommt mir das doch noch ein wenig seltsam vor.

Schließlich habe ich dem Feld diese Eigenschaft zugewiesen: IBTable1.AddIndex('UNIQUE', 'NAMES', [ixUnique, ixCaseInsensitive]);

CaseInsensitive bedeutet ja das DER_NAME und der_name gleich sind !


Brauch ich diese Eigenschaften überhaupt noch wenn ich im neuen Table den PK auf das Feld lege ?

[mkinzler]

Der Unique Index gilt ja auch für das Feld NAMES und nicht VORNAME

[-187-]

Das Feld Vornamen war nur ein Bsp. Genauso wie das "Namen"

In Wirklichkeit heißen meine Felder anders - Ich bin mir aber sicher das ich den Index auf das richtige Feld gesetzt habe !

[-187-]

Code:

IBTable1.AddIndex('UNIQUE', 'NAMES', [ixUnique, ixCaseInsensitive]);

Hm eigentlich habe ich doch somit einen Index und zwar Names "UNIQUE" !

Ist es nicht möglich diese für den Duplicate Check zur verwenden ?

[fkerber]

Hi!

Bitte unterlasse das Pushen innerhalb von 24 Std.

Sollte es einen UNIQUE-Index auf dem Feld geben, wird es sicherlich keine Duplikate enthalten.


Liebe Grüße,
Frederic