Wir wollen einen Unique-Index in einer Tabelle anlegen, in der es Duplikate gibt. Bevor der Index angelegt werden kann, müssen die Duplikate identifiziert und gelöscht werden.
Damit wir das Testen können, legen wird ein paar Testdaten an.
# Erzeuge eine Demo-Tabelle
create table idgen (id_name varchar(255), id_value integer)
# Erzeuge Dummy-Daten
insert into idgen values ( 'a', 80);
insert into idgen values ( 'b', 80);
insert into idgen values ( 'b', 90);
insert into idgen values ( 'b', 100);
insert into idgen values ( 'c', 55);
insert into idgen values ( 'c', 66);
insert into idgen values ( 'c', 66);
insert into idgen values ( 'd', 55);
insert into idgen values ( 'd', 66);
Leider ist die Syntax abhängig vom DBMS. Hier verwenden wir Oracle und den MS SQL Server.
In Oracle
# Ausgabe der Row-Ids die gelöscht werden soll
WITH A AS (
SELECT f.id_name, id_value,
row_number() OVER (PARTITION BY id_name order by id_name) c,
sum(1) over (partition by id_name order by id_name) m,
rowid rid
FROM idgen f
)
SELECT id_name, id_value, c, m, rid
FROM A
WHERE C<>M
# Und als Ready-To-Use DELETE-Statement
DELETE FROM idgen where rowid in (
WITH A AS (
SELECT f.id_name, id_value,
row_number() OVER (PARTITION BY id_name order by id_name) c,
sum(1) over (partition by id_name order by id_name) m,
rowid rid
FROM idgen f
)
SELECT rid
FROM A
WHERE C<>M
)
Im MS SQL-Server
# Im SQL-Server wird rowid() zu %%pysloc%%
WITH A AS (
SELECT f.id_name, id_value,
row_number() OVER (PARTITION BY id_name order by id_name) c,
sum(1) over (partition by id_name order by id_name) m,
%%physloc%% rid
FROM idgen f
)
SELECT id_name, id_value, c, m, rid
FROM A
WHERE C<>M
# Und das Delete-Statement sieht etwas anders aus
DELETE FROM idgen where %%physloc%% in (
select rid from (
SELECT f.id_name, id_value,
row_number() OVER (PARTITION BY id_name order by id_name) c,
sum(1) over (partition by id_name order by id_name) m,
%%physloc%% rid
FROM idgen f
) r
WHERE C<>M
)