none
Wie kann man in einer Tabelle ohne Spalte mit unique-Werten Duplikate entfernen? RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    ich möchte aus folgender Tabelle die Duplikate das Flag [IsDup] auf True/1 setzen:

    GO
CREATE TABLE [dbo].[TableDup](
	[Name1] [nvarchar](50) NULL,
	[Name2] [nvarchar](50) NULL,
	[Datum] [datetime] NULL,
	[InFz] [bit] NULL,
	[IsDup] [bit] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[TableDup] ([Name1], [Name2], [Datum], [InFz], [IsDup]) VALUES (N'A', N'B', CAST(N'2018-12-01T12:00:00.000' AS DateTime), 1, NULL)
INSERT [dbo].[TableDup] ([Name1], [Name2], [Datum], [InFz], [IsDup]) VALUES (N'B', NULL, CAST(N'2018-12-02T11:00:00.000' AS DateTime), 0, NULL)
INSERT [dbo].[TableDup] ([Name1], [Name2], [Datum], [InFz], [IsDup]) VALUES (N'A', N'B', CAST(N'2018-12-01T12:00:00.000' AS DateTime), 0, NULL)
INSERT [dbo].[TableDup] ([Name1], [Name2], [Datum], [InFz], [IsDup]) VALUES (N'A', N'B', CAST(N'2018-12-01T12:00:00.000' AS DateTime), 1, 0)
INSERT [dbo].[TableDup] ([Name1], [Name2], [Datum], [InFz], [IsDup]) VALUES (N'C', N'D', CAST(N'2018-12-10T11:00:00.000' AS DateTime), 0, NULL)
INSERT [dbo].[TableDup] ([Name1], [Name2], [Datum], [InFz], [IsDup]) VALUES (N'A', N'B', CAST(N'2018-12-01T12:00:00.000' AS DateTime), 1, 0)

    Mit folgender Query:

    Update [TestTabellenDB].[dbo].[TableDup]
SET [IsDup] = 1
WHERE [Name1] NOT IN
(
	SELECT First_Value(x.[Name1]) OVER (ORDER BY x.[Name2] ASC) AS [kein Dup]
	FROM [TestTabellenDB].[dbo].[TableDup] AS x
	GROUP BY x.Name1, x.Name2, x.Datum, x.InFz
)

    funktioniert es nicht. Ich hatte es auch mit ROW_NUMBER() probiert, aber auch das geht nicht.

    Hätte die Tabelle eine Spalte mit unique-Werten, z.B. [ID], so würde es mit:

    Update [TestTabellenDB].[dbo].[TableDup]
SET [IsDup] = 1
WHERE [ID] NOT IN
(
	SELECT x.[ID] AS [kein Dup]
	FROM [TestTabellenDB].[dbo].[TableDup] AS x
	GROUP BY x.Name1, x.Name2, x.Datum, x.InFz
)

    wunderbar funktionieren.

    Hat hier jemand eine Idee, wie das gehen kann?

    Gruß

    Jürgen

    Montag, 10. Dezember 2018 06:38
    |

Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Jürgen,

    das geht mit ROW_NUMBER, wenn ich es richtig verstanden habe, ist Name1 ausschlaggebend (PARTITION):

    With Duplikate as
(
Select Name1, Name2, Datum, InFz, IsDup, ROW_NUMBER() OVER(PARTITION BY Name1 ORDER BY Name2, Datum, InFz) as rn
from TableDup
)
Update Duplikate
set IsDup = case when rn = 1 then 0 else 1 end
;

Select *
from TableDup
order by Name1, Name2, Datum, InFz;

    Siehe auch: Duplikate entfernen

    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert Jürgen Sch Montag, 10. Dezember 2018 10:24
    Montag, 10. Dezember 2018 08:53
    |
    Beantworter

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Jürgen,

    da dich die fehlende Eindeutigkeit beim aktualisieren und löschen von Datensätzen aus dieser Tabelle wohl immer wieder vor Probleme stellen wird, wäre es sinnvoll, wenn Du eine neue Spalte (INT) mit IDENTITY Flag oder, falls das dir lieber ist, mit einer Guid und NEWID() als Defaultwert, in die Tabelle einzufügen.

    Notfalls nur für das UPDATE Statement. Also Identity Spalte einfügen, Update durchführen, Spalte wieder entfernen.

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Montag, 10. Dezember 2018 08:27
    |
    Moderator
  • Question
    Anmelden
    0
    Anmelden

    Hallo Jürgen,

    das geht mit ROW_NUMBER, wenn ich es richtig verstanden habe, ist Name1 ausschlaggebend (PARTITION):

    With Duplikate as
(
Select Name1, Name2, Datum, InFz, IsDup, ROW_NUMBER() OVER(PARTITION BY Name1 ORDER BY Name2, Datum, InFz) as rn
from TableDup
)
Update Duplikate
set IsDup = case when rn = 1 then 0 else 1 end
;

Select *
from TableDup
order by Name1, Name2, Datum, InFz;

    Siehe auch: Duplikate entfernen

    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert Jürgen Sch Montag, 10. Dezember 2018 10:24
    Montag, 10. Dezember 2018 08:53
    |
    Beantworter
  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    danke zunächst für Eure Antworten.

    @Stefan: Du hast recht, nur müssten wir dann unsere ganze Datenbank wieder ändern, das müsste ich erst abklären.

    @Christoph: Deine Lösung funktioniert soweit. Ich würde nun gerne die Anzahl der tatsächlichen Änderungen ausgeben. Mit @@ROWCOUNT zeigt er mir immer 6 an, und selbst wenn ich die cte mit WHERE IsDup <> '1' einschränke wird immer 3 ausgegeben, was ja auch richtig ist, da es nach einem Duplikat-Update noch 3 Nicht-Duplikate gibt. Da ich in der View für den User jedoch anzeigen will, ob und wie  viele "neue" Duplikate gefunden wurden, bräuchte ich da noch einen Zähler. Geht das???

    Gruß

    Jürgen

    Montag, 10. Dezember 2018 09:41
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo Christoph,

    ich habe es mit 

    Update Duplikate
set IsDup = 1
WHERE rn NOT in
(
	SELECT FIRST_VALUE(x.rn) OVER (ORDER BY x.Name2) AS [kein Dup]
	FROM Duplikate AS x
	GROUP BY x.Name1, x.Name2, x.Datum, x.InFz, x.rn
)
;

PRINT @@ROWCOUNT

    hinbekommen ;-)

    danke an alle

    Gruß

    Jürgen

    Montag, 10. Dezember 2018 10:24
    |
  • Question
    Anmelden
    0
    Anmelden

    Sorry,

    da war ich wohl zu voreilig :-(

    Es funktioniert doch nicht wie erhofft.

    Ein Duplikat ist definiert, wenn die Werte in [Name1], [Name2], [Datum] und [InFz] identisch sind.

    Leider wird InFz nicht geprüft.

    Was muss ich ergänzen?

    Gruß

    Jürgen

    Montag, 10. Dezember 2018 10:55
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    jetzt habe ich es:

    With Duplikate as
(
Select *, ROW_NUMBER() OVER(PARTITION BY Name1, Datum, InFz ORDER BY Name2, Datum, InFz) as rn
from TableDup
WHERE IsDup <> '1'
)

--SELECT * FROM Duplikate

Update Duplikate
set IsDup = 1
WHERE rn NOT in
(
	SELECT FIRST_VALUE(x.rn) OVER (PARTITION BY x.Name1, x.Datum, x.InFz ORDER BY x.Name2, x.Datum, x.InFz) AS [kein Dup]
	FROM Duplikate AS x
	GROUP BY x.Name1, x.Name2, x.Datum, x.InFz, x.rn
)
;

PRINT @@ROWCOUNT

    Die für die Duplikate relevanten Spalten müssen mit in den OVER(PARTITION BY... Bereich rein.

    Gruß Jürgen

    Montag, 10. Dezember 2018 11:10
    |