T-SQL: ROW_NUMBER() liefert in Kombination mit UPDATE immer 1 @CODEKICKER

T-SQL: ROW_NUMBER() liefert in Kombination mit UPDATE immer 1

Hallo,
ich möchte eine Spalte einer Tabelle mit einer fortlaufenden (eindeutigen) Nummer updaten.

Folgendes Statement liefert die gewünschte Nummerierung:

SELECT *, ROW_NUMBER() OVER(ORDER BY Z_ZeitraumVon DESC, Z_ZeitraumBis DESC) AS NeuePosition
 FROM tbl_Zuordnung
 WHERE (Z_MA_PrimKey = 5)
 ORDER BY NeuePosition

In Kombination mit einem UPDATE-Statement ist NeuePosition immer 1. Ich habe zwei Varianten probiert:

UPDATE tbl_Zuordnung
 SET Z_Position = tblSorted.NeuePosition
 FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY Z_ZeitraumVon DESC, Z_ZeitraumBis DESC) AS NeuePosition
  FROM tbl_Zuordnung
  WHERE Z_MA_PrimKey=5
   ) AS tblSorted
 WHERE tbl_Zuordnung.Z_MA_PrimKey=5

oder

WITH cteSortiert
AS
(
  SELECT *, ROW_NUMBER() OVER(ORDER BY Z_ZeitraumVon DESC, Z_ZeitraumBis DESC) AS NeuePosition
 FROM tbl_Zuordnung
 WHERE Z_MA_PrimKey=5
)

UPDATE tbl_Zuordnung
  SET Z_Position = cteSortiert.NeuePosition
  FROM cteSortiert
  WHERE tbl_Zuordnung.Z_MA_PrimKey=5

Warum ist das so? Was kann ich tun?
Bin für alle Tipps dankbar.
Liebe Grüße, Tom

07.06.2011

Metal-Frog

731 ● 7

2 Antworten

Hallo,

das Verhalten kann ich so nicht nachvollziehen. Folgendes Script liefert das gewünschte Ergebnis:

CREATE TABLE Test (Name VARCHAR(40), Number INT)

INSERT INTO Test(Name)
SELECT name FROM master.sys.objects

;WITH TempCTE AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY Name) AS SortedNumber FROM Test
)
UPDATE x SET
x.Number = c.SortedNumber
FROM Test x
INNER JOIN TempCTE c ON (c.Name = x.Name) 

SELECT * FROM Test

DROP TABLE Test

08.06.2011

Khalid

490 ● 2

		Hallo Khalid, vielen Dank. Deine Lösung sieht genauso aus, wie ich mir das vorgestellt habe. Leider funktioniert sie in meinem Fall nicht. Ich habe das mal in mein SQL übertragen, trotzdem wird immer nur eine 1 geschrieben. Vielleicht siehst du ja den Fehler: ;WITH cteSortiert AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY Z_ZeitraumVon DESC, Z_ZeitraumBis DESC) AS NeuePosition FROM tbl_Zuordnung WHERE Z_MA_PrimKey=5 ) UPDATE x SET x.Z_DE_Tätigkeit = c.NeuePosition FROM tbl_Zuordnung x INNER JOIN cteSortiert c ON (c.Z_MA_PrimKey = x.Z_MA_PrimKey) Danke & Gruß, Tom – Metal-Frog 09.06.2011
		Hab's gefunden. Mein Inner Join war nicht eindeutig! Funktioniert also. Vielen Dank! – Metal-Frog 09.06.2011

Du könntest es mit einer TempTabelle lösen:

insert into #t
SELECT *, ROW_NUMBER() OVER(ORDER BY Z_ZeitraumVon DESC, Z_ZeitraumBis DESC) AS NeuePosition
        FROM    tbl_Zuordnung
        WHERE   (Z_MA_PrimKey = 5)
        ORDER BY NeuePosition

UPDATE tbl_Zuordnung
        SET Z_Position = t.NeuePosition
        FROM #t t
        WHERE tbl_Zuordnung.Z_MA_PrimKey=5

drop table #t