Tabellenwert aktualisieren

Question

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.

Ich habe schon einiges ausprobiert (z.B. via  ROW_NUMBER() OVER(PARTITION ...), aber leider ohne Erfolg. Ich hoffe, es geht irgendwie über eine Gruppierung. Aber vielleicht kann man das nur mit einem Cursor lösen. Aber damit habe ich fast keine Erfahrungen.

Wer hat dazu eine Idee?

Answer 1

Hallo KlausDieter,

eine Garantie, dass das so funktioniert, kann dir keiner geben. Denn letztendlich musst Du, damit Du eine nicht willkürliche Reihenfolge hast, die Werteliste sortieren. Sowohl die Quell- als auch die Zieldatensätze.

Woher weißt Du eigentlich, dass IDRes 20545 für DNo 100089 wirklich in der Zieltabelle für IDreq 4460 zugeordnet werden muss. Es könnte ja genausogut der Datensatz mit IDreq 45737 sein!? Falls das nicht in ausnahmslos allen Fällen gegeben ist, geht das, was Du vorhast, sowieso nicht.

Wenn deine Quelltabelle genau so sortiert sein muss, füge eine neue Spalte für die Sortierung ein. Ich mache das in der Regel so, dass ich eine neue IDENTITY Spalte einfüge. Bisher habe ich noch nicht feststellen können, dass die dann automatisch erzeugten Werte nicht stimmen. Dasselbe dann bei der Zieltabelle.

Wenn die Reihenfolge und die Anzahl der Datensätze 100%ig übereinstimmen, kannst Du dann über die neuen Spalten die Datensätze verbinden und die Daten aktualisieren.

UPDATE T_Ziel
SET    T_Ziel.IDres = T_Quelle.IDres
FROM   T_Quelle
WHERE  T_Ziel.<IDENTITY_SPALTE> = T_Quelle.<IDENTITY_SPALTE>

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Hallo Stefan,

vielen Dank für die Antwort. Ja natürlich, wenn die Anzahl identisch wäre, dann ginge es so. Das ist aber leider nicht (ganz) der Fall.

Ich muss in der req-Tabelle (das ist die obere) pro DNo den ersten Wert nehmen und mit dem ersten Wert der res-Tabelle (also die untere) verknüpfen. Dann den zweiten aus req mit dem zweiten aus req usw.. In der res Tabelle können noch Werte fehlen, dann bleiben die Pendents null. Die IDs sind in beiden Tabellen chronologisch nach zeitlicher Folge erstellt. In beiden Tabelle gibt es zudem je eine Zeitspalte. Es gilt pro DNo: der ältere Wert aus req wird mit dem ältesten aus res verknüpft, der zweitälteste aus Req mit dem zweitältesten aus res usw. Man könnte also alternativ statt der oben aufgeführten IDs auch mit den Zeitwerten arbeiten.

Gruß Klaus

Answer 3

Hallo Klaus,

klar, wenn Du noch einen Wert hast, nach dem Du sortieren kannst, wäre das natürlich einfacher.

Poste doch bitte mal die vollständige Tabellenstruktur mit einigen Datensätzen. Evtl. findet sich ja hier noch etwas, was noch besser verwendbar ist.

Letztendlich musst Du dafür sorgen, dass Du ein eindeutiges Kriterium in beiden Tabellen hast, über das verknüpft werden kann. Gibt es das nicht, geht es nicht bzw. muss man sich dann ein solches Krtiterium "basteln".

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 4

Hallo Klaus Dieter,

Stefan hat den entscheidenden Hinweis eigentlich schon gegeben. Du benötigst mehr als nur die DNo als eindeutiges Kriterium für die Definition der Schnittmenge. In Deinem Fall wäre das ein Datumsattribut, das für eine Sequenz sorgt. Im Anschluß daran ist auf jeden Fall Dein Ansatz der ROW_NUMBER eine gute Lösung. Beides gemeinsam führt dann m. E. zu einer adäquaten Lösung wie nachfolgend als Beispiel dargelegt:

USE tempdb
GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.table1', 'U') IS NOT NULL
	DROP TABLE dbo.table1
	GO

IF OBJECT_ID('dbo.table2', 'U') IS NOT NULL
	DROP TABLE dbo.table2
	GO

CREATE TABLE dbo.table1
(
	DNo	int	NOT NULL,
	IDres int	NULL,
	Idreq	int	NOT NULL,
	InsertDate	datetime	NOT NULL	DEFAULT (getdate())
);

CREATE TABLE dbo.table2
(
	DNo	int	NOT NULL,
	IDres int	NULL,
	InsertDate	datetime	NOT NULL	DEFAULT (getdate())
);

INSERT INTO dbo.table1 (DNo, IDRes, IDReq) VALUES (10004, NULL, 44725);
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table1 (DNo, IDRes, IDReq)  VALUES (100009, NULL, 31362);
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table1 (DNo, IDRes, IDReq)  VALUES (100009, NULL, 34573)
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table1 (DNo, IDRes, IDReq)  VALUES (100016, NULL, 4459)
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table1  (DNo, IDRes, IDReq) VALUES (100039, NULL, 19639)
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table1 (DNo, IDRes, IDReq) VALUES (100070, NULL, 706)
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table1 (DNo, IDRes, IDReq) VALUES (100089, NULL, 4460)
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table1 (DNo, IDRes, IDReq) VALUES (100089, NULL, 45737)

WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (10004, 43121); 
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (100009, 33366);
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (100009, 35624); 
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (100016, 2114);
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (100039, 13804); 
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (100070, 11508);
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (100089, 20545);
WAITFOR DELAY '00:00:01';
INSERT INTO dbo.table2 (DNo, IDRes) VALUES (100089, 45738);

WITH t
AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY DNo ORDER BY InsertDate ASC) AS Id,
	DNo, IDRes, IDreq
	FROM dbo.table1
),
s AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY DNo ORDER BY InsertDate ASC) AS Id,
	DNo, IDRes
	FROM dbo.table2
)
UPDATE	t
		SET	t.IdRes = s.IdRes
FROM	t INNER JOIN s
		ON	(
				t.id = s.id AND
				t.DNo = s.DNo
			);

SELECT * FROM dbo.table1;
SET NOCOUNT OFF;

Ich habe in dem Beispiel eine CTE über beide Relationen erzeugt, die auf Basis der DNo partitioniert ist und über das [fiktive] Datum sortiert wird. Das gleiche gilt auch für die zweite Relation! Durch die Gruppierung habe ich nun einen einedeutigen Schlüssel über den ich einen JOIN ausführen kann.

---

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 5

Hallo Klaus,

könnte Dir das hier helfen? Ich habe noch ein paar Beispieldaten ergänzt.

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;

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

Answer 6

(Diesen Text habe ich bereits heute Vormittag posten wollen, aber ich konnte nichts abspeichern)

@ Uwe und Christoph

zunächst mal: Da habt ihr euch ja richtig Mühe gemacht, die Tabellen anzulegen. Mir ist das eine Lehre: Beim nächsten Mal liefere ich gleich wie ihr Create-Anweisungen mit. Ich bevorzuge übrigens Temp-Tabellen (also die mit dem #).

OK, vielen Dank für eure spaßiger Weise fast identischen Tipps. Ja ich war mit "Row-Number over" auf dem richtigen Weg. Mein Fehler war, dass ich es nur mit einer Tabelle lösen wollte - klar, man braucht beide.

Das mit "with" kannte ich übrigens nicht - ich habe sowas bisher immer über Temp-Tabellen gelöst. Aber so geht es elganter - prima, da habe ich gleich noch was neues gelernt!

@ Stephan

Auch dir vielen Dank. Das mit der Tabellenstruktur hätte nichts gebracht, denn ich habe alles Wesentliche (nur stark vereinfacht) bereits gepostet. Aber das Problem ist jetzt ja gelöst

Answer 7

Ich vermute mal, dass Uwe und ich auch noch beide den gleichen Trick angewendet haben, der für die Erstellung solcher Skripte sehr wertvoll ist:
http://www.insidesql.org/blogs/cmu/sql_server/spaltenweises-editieren-im-ssms-2012

Man sollte aber schon SSMS 2012 am Start haben...

Toll wäre jetzt noch, wenn Du die antworten, die Dir geholfen haben auch als solche markierst, damit der Thread insgesamt geschlossen erscheint.

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

Answer 8

Ich vermute mal, dass Uwe und ich auch noch beide den gleichen Trick angewendet haben, der für die Erstellung solcher Skripte sehr wertvoll ist:
http://www.insidesql.org/blogs/cmu/sql_server/spaltenweises-editieren-im-ssms-2012/

Das liebe ich so an diesem Forum. Du glaubst schon - fast - alles zu wissen (es sei denn man liest auf SQLSKILLS :) und dann kommt da mal locker fluffig ein so genialer Tipp daher.

Danke Christoph - das kannte ich noch nicht. Wird aber von nun an etwas häufiger zum Einsatz kommen :)

---

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 9

Prima!
Man sieht sich! Nächste Woche.

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