[How-to] Spaltennummerierung, Zeilennummer

[ukulele]

Da ich es selbst immer wieder raus krame hier mal ein unter MS SQL sehr nützlicher Code. Will man per Schleife eine Tabelle oder eine Teilmenge davon durchlaufen die keine Zeilennummerierung (z.B. in Form eines fortlaufenden Integers) hat, kann man sich wie folgt behelfen:

DECLARE    @pk UNIQUEIDENTIFIER,
        @counter INT
 
SET        @counter = (    SELECT    count(1)
                        FROM    tabelle )
WHILE    @counter > 0
BEGIN
    SET        @pk = (    SELECT    pk
                    FROM (    SELECT    TOP (@counter) pk,
                                    ROW_NUMBER() OVER (ORDER BY pk) AS zeilennr
                            FROM    tabelle
                            WHERE  pk = pk -- optionale WHERE-Bedigungen
                            ORDER BY pk ) x
                    WHERE    x.zeilennr = @counter )
 
    SELECT    @pk,
            @counter
 
    SET        @counter -= 1
END

Natürlich läßt sich die Sortierung beliebig ändern. Auch läßt sich OVER() für eine simple Zeilennummer nutzen:

SELECT    pk,
        ROW_NUMBER() OVER (ORDER BY pk) AS zeilennr
FROM    tabelle
ORDER BY pk

 

[akretschmer]

Natürlich läßt sich die Sortierung beliebig ändern. Auch läßt sich OVER() für eine simple Zeilennummer nutzen:

Das sieht nicht nur deutlich angenehmer aus, sondern ist auch so der Weg unter Oraggle und PG und so auch in irgend einer SQL-Spec ...

 

[ukulele]

Jetzt hab ich zum selben Thema ein Problem. Ich möchte eine Tabelle sortieren und die Sortierung in die dafür vorgesehene, bereits vorhande aber nicht komplett gefüllte Spalte schreiben.

SELECT    pk,zeilennr
FROM    kam_zie

6327F126-018B-BE42-9421-3281ED847093 NULL
85FC9189-C711-7D42-AA7C-405FA2FF1D58 NULL
D112C1FB-9F72-DB45-9C3B-4532BF3B8023 1
8DF98D2B-7741-0D41-A004-793F9D90A21A 1
637BD8C1-695E-9442-9CB7-F2001E63A09C 2
DDD0E6EF-3C42-9647-B406-FE486F80E2FF 3
CEAD095A-C066-404D-84F4-1EF591CF195D 7

SELECT    pk,
        zeilennr,
        (    SELECT    t1.zeilennr_neu
            FROM    (    SELECT    t2.pk,
                                ROW_NUMBER() OVER (ORDER BY zeilennr) AS zeilennr_neu
                                FROM    kam_zie t2 ) t1
                        WHERE    t1.pk = kam_zie.pk ) AS asdf
FROM    kam_zie
ORDER BY zeilennr

funktioniert und ergibt:

6327F126-018B-BE42-9421-3281ED847093 NULL 1
85FC9189-C711-7D42-AA7C-405FA2FF1D58 NULL 2
D112C1FB-9F72-DB45-9C3B-4532BF3B8023 1 3
8DF98D2B-7741-0D41-A004-793F9D90A21A 1 4
637BD8C1-695E-9442-9CB7-F2001E63A09C 2 5
DDD0E6EF-3C42-9647-B406-FE486F80E2FF 3 6
CEAD095A-C066-404D-84F4-1EF591CF195D 7 7

Aber wenn ich das ganze per Update mache:

UPDATE    kam_zie
SET        zeilennr =(    SELECT    t1.zeilennr_neu
                    FROM    (    SELECT    t2.pk,
                                ROW_NUMBER() OVER (ORDER BY zeilennr) AS zeilennr_neu
                                FROM    kam_zie t2 ) t1
                    WHERE    t1.pk = kam_zie.pk )

Meldung 512, Ebene 16, Status 1, Prozedur kam_zie_gen_zeilennr, Zeile 8
Die Unterabfrage hat mehr als einen Wert zurückgegeben. Das ist nicht zulässig, wenn die Unterabfrage auf =, !=, <, <=, > oder >= folgt oder als Ausdruck verwendet wird.
Die Anweisung wurde beendet.

t1 liefert definitiv nur eine zeilennr_neu pro pk und eine andere Unterabfrage mit = habe ich nicht. Natürlich kann ich jetzt auf eine Hilfsspalte ausweichen aber ich verstehe das nicht so recht.

PS: Die Fehlermeldung scheint mir nicht sinnig, es scheint mehr das kam_zie.pk in der Unterabfrage nicht funktioniert. Aber auch wenn ich dort eine UUID fest eintrage und die Unterabfrage definitiv funktioniert und nur den Wert 6 liefert läuft das Update Statement nicht. Offensichtlich verändert sich die Tabelle mit jeder Zeile für die das Update Statement ausgeführt wird.

 

[akretschmer]

Jetzt hab ich zum selben Thema ein Problem. Ich möchte eine Tabelle sortieren und die Sortierung in die dafür vorgesehene, bereits vorhande aber nicht komplett gefüllte Spalte schreiben.

SELECT    pk,zeilennr
FROM    kam_zie

SELECT    pk,
        zeilennr,
        (    SELECT    t1.zeilennr_neu
            FROM    (    SELECT    t2.pk,
                                ROW_NUMBER() OVER (ORDER BY zeilennr) AS zeilennr_neu
                                FROM    kam_zie t2 ) t1
                        WHERE    t1.pk = kam_zie.pk ) AS asdf
FROM    kam_zie
ORDER BY zeilennr

funktioniert und ergibt:

Aber wenn ich das ganze per Update mache:

UPDATE    kam_zie
SET        zeilennr =(    SELECT    t1.zeilennr_neu
                    FROM    (    SELECT    t2.pk,
                                ROW_NUMBER() OVER (ORDER BY zeilennr) AS zeilennr_neu
                                FROM    kam_zie t2 ) t1
                    WHERE    t1.pk = kam_zie.pk )

t1 liefert definitiv nur eine zeilennr_neu pro pk und eine andere Unterabfrage mit = habe ich nicht. Natürlich kann ich jetzt auf eine Hilfsspalte ausweichen aber ich verstehe das nicht so recht.

PS: Die Fehlermeldung scheint mir nicht sinnig, es scheint mehr das kam_zie.pk in der Unterabfrage nicht funktioniert. Aber auch wenn ich dort eine UUID fest eintrage und die Unterabfrage definitiv funktioniert und nur den Wert 6 liefert läuft das Update Statement nicht. Offensichtlich verändert sich die Tabelle mit jeder Zeile für die das Update Statement ausgeführt wird.

Ich würde es so machen und denke, M$SQL kann das auch:

test=*# create table ukulele (id int, nr int);
CREATE TABLE
Time: 18,828 ms
test=*# insert into ukulele (id) select s from generate_Series(1,10) s;
INSERT 0 10
Time: 0,444 ms
test=*# select * from ukulele ;
 id | nr
----+----
  1 |
  2 |
  3 |
  4 |
  5 |
  6 |
  7 |
  8 |
  9 |
 10 |
(10 rows)

Nun das Update:

test=*# with foo as (select id, row_number() over (order by id) as nr from ukulele) update ukulele set nr = foo.nr from foo where ukulele.id=foo.id;
UPDATE 10
Time: 0,462 ms
test=*# select * from ukulele ;
 id | nr
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |  5
  6 |  6
  7 |  7
  8 |  8
  9 |  9
 10 | 10
(10 rows)

 

[ukulele]

Bei deiner Lösung bekomme ich exakt den selben Fehler. = foo.id liefert offenbar mehr als einen Wert (das behauptet die Fehlermeldung).

Ich habe die Sortierung in eine Temp Tabelle geschrieben und nichtmal das läßt sich zurück schreiben:

        UPDATE    kam_zie
        SET        zeilennr = (    SELECT    zeilennr
                                FROM    kam_zie_temp
                                WHERE    pk = kam_zie.pk )

Bin ich blöd oder habe ich Update verlernt? Bin ich eigentlich schon wach?

 

[akretschmer]

Bei deiner Lösung bekomme ich exakt den selben Fehler. = foo.id liefert offenbar mehr als einen Wert (das behauptet die Fehlermeldung).

Ich habe die Sortierung in eine Temp Tabelle geschrieben und nichtmal das läßt sich zurück schreiben:

        UPDATE    kam_zie
        SET        zeilennr = (    SELECT    zeilennr
                                FROM    kam_zie_temp
                                WHERE    pk = kam_zie.pk )

Bin ich blöd oder habe ich Update verlernt? Bin ich eigentlich schon wach?

works for me:

test=*# select * from ukulele ;   
 id | nr   
----+----   
  1 |  1   
  2 |  2   
  3 |  3   
  4 |  4   
  5 |  5
  6 |  6
  7 |  7
  8 |  8
  9 |  9
 10 | 10
(10 rows)

Time: 0,181 ms
test=*# select * from u2;
 id | nr
----+----
  1 |
  2 |
  3 |
  4 |
  5 |
  6 |
  7 |
  8 |
  9 |
 10 |
(10 rows)

Time: 0,195 ms
test=*# update u2 set nr = ( select nr from ukulele where id=u2.id);
UPDATE 10
Time: 0,371 ms
test=*# select * from u2;
 id | nr
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |  5
  6 |  6
  7 |  7
  8 |  8
  9 |  9
 10 | 10
(10 rows)

Time: 0,178 ms

 

[ukulele]

Es läuft jetzt im Trigger unter zur Hilfenahme einer Temp Tabelle. Also das besagte Update Statement aus meinen Vorpost geht jetzt, ich habe eigentlich nichts verändert nur einen select zum debuggen dazwischen gesetzt und wieder entfernt.