« SSDT BI for Visual Studio 2012
Man sieht sich auf den Frankfurter Datenbanktagen 2013 »

Update in bestimmter Reihenfolge

Heute gab es im Forum mal wieder eine interessante Frage, die darauf zielte Sätze aus einer Tabelle in einer bestimmten Reihenfolge mit Sätzen aus einer anderen Tabelle zu aktualisieren.

Ausgangslage

Ich gebe hier das Posting einfach mal wieder:

Ich habe eine Tabelle, wo ich ein Feld mit den zugehrigen IDs aus einer anderen aktualisieren muss.

Die Zieltabelle sieht so aus (IDRes muss aktualisiert werden):
DNo       IDres   IDreq
10004     NULL    44725
100009    NULL    31362
100009    NULL    34573
100016    NULL    4459
100039    NULL    19639
100070    NULL    706
100089    NULL    4460
100089    NULL    45737

Die Quelltabelle so:

DNo       IDres    
10004     43121    
100009    33366  
100009    35624    
100016    2114   
100039    13804    
100070    11508    
100089    20545   
100089    45738   

Die Aufgabe ist, die IDres-Werte aus der unteren Tabelle in genau dieser Reihenfolge in die obere in das gleichnamige Feld einzufügen (zu aktualisieren).  Oder anders  ausgedrückt: Pro DNo müssen die IDres-Werte aufsteigend sortiert aus der unteren Tabelle eingefügt werden.

Im weiteren Verlauf wurde dann noch klar, dass es kein gemeinsames Kriterium für die Reihenfolge gab und auch nicht zu jedem Satz aus der ersten Tabelle ein Wert in der zweiten Tabelle vorhanden sein musste. Diese Zeilen sollten dann NULL bleiben.

Die Lösung mit einer Window Function

An dieser Stelle greifen wir auf eine Window Function zurück, die uns seit der Version SQL Server 2008 zur Verfügung steht.

Man kommt schnell auf die Idee, dies über ROW_NUMBER() zu lösen, wobei der Trick darin besteht, diese Window Function bereits in einer Common Table Expression (CTE) anzuwenden und das UPDATE auf die CTE durchzuführen. Das funktioniert, auch wenn die per ROW_NUMBER() ermittelte Spalte natürlich nicht aktualisiert werden kann.

Ich habe die Beispieldaten entsprechend ergänzt und gebe hier das Skript für die Lösung wieder:

DECLARE @Tab1 AS TABLE (DNo integer, IDres integer, IDreq integer);
DECLARE @Tab2 AS TABLE (DNo integer, IDres integer);

INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(10004 ,   NULL ,   44725);
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100009,   NULL ,   31362);
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100009,   NULL ,   34573);
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100009,   NULL ,   99573);
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100016,   NULL ,   4459 );
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100039,   NULL ,   19639);
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100039,   NULL ,   19640);
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100039,   NULL ,   19650);
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100070,   NULL ,   706  );
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100089,   NULL ,   4460 );
INSERT INTO @Tab1(DNo, IDres, IDreq) VALUES(100089,   NULL ,   45737);

INSERT INTO @Tab2(DNo, IDres) VALUES(10004 ,   43121);
INSERT INTO @Tab2(DNo, IDres) VALUES(100009,   33366);
INSERT INTO @Tab2(DNo, IDres) VALUES(100009,   35624);
INSERT INTO @Tab2(DNo, IDres) VALUES(100016,   2114 );
INSERT INTO @Tab2(DNo, IDres) VALUES(100039,   13804);
INSERT INTO @Tab2(DNo, IDres) VALUES(100039,   12804);
INSERT INTO @Tab2(DNo, IDres) VALUES(100039,   11804);
INSERT INTO @Tab2(DNo, IDres) VALUES(100070,   11508);
INSERT INTO @Tab2(DNo, IDres) VALUES(100089,   20545);
INSERT INTO @Tab2(DNo, IDres) VALUES(100089,   45738);

SELECT DNo, IDres, IDReq, ROW_NUMBER() OVER(PARTITION BY DNo ORDER BY IDReq) AS rrn
FROM @Tab1
ORDER BY DNo, IDreq;

SELECT DNo, IDres, ROW_NUMBER() OVER(PARTITION BY DNo ORDER BY IDRes) AS rrn
FROM @Tab2
ORDER BY DNo, IDres;

WITH T1 AS
(SELECT DNo, IDres, IDReq, ROW_NUMBER() OVER(PARTITION BY DNo ORDER BY IDReq) AS rrn
FROM @Tab1
),
T2 AS
(SELECT DNo, IDres, ROW_NUMBER() OVER(PARTITION BY DNo ORDER BY IDRes) AS rrn
FROM @Tab2
)
UPDATE T1
SET IDres = T2.IDres
FROM T1 INNER JOIN T2
ON T1.DNo = T2.DNo
AND T1.rrn = T2.rrn;

SELECT *
FROM @Tab1
ORDER BY DNo, IDreq;
entwicklung, t-sql, window functions
SQL Server
 Print article This entry was posted by cmu on 07.03.13 at 09:04:00 . Follow any responses to this post through RSS 2.0.