[SQL] Order-Feld automatisch ausfüllen lassen

[Valle]

Hallo DPler!

Ich hab' mal wieder eine Frage, ob etwas sinnvoll nur mit SQL umsetzbar ist.

Ich habe eine Tabelle mit den Feldern Name und Order. Bei jeder Abfrage wird die Reihenfolge der Einträge über das Feld Order bestimmt (ORDER BY `order`). Manchmal kommt es aber vor, dass die Einträge dieser Tabelle völlig durcheinander sind und die Reihenfolge automatisch neu bestimmt werden kann. In diesem Fall ist die Abfrage über ORDER BY `name` richtig. Da die Abfragen aber nicht allesamt geändert werden können, muss das der Inhalt des Order-Feld neu gesetzt werden. Folgender Code tut dies:

Code:

result = connection.execute("SELECT id FROM tabelle ORDER BY name")
order = 1
for row in result:
    connection.execute("UPDATE tabelle SET `order` = %i WHERE id = %i" % (order, row['id']))
    order += 1

Gibt es einen sinnvollen Code, der dies auf dem Server ausführt? Das ganze muss unter MySQL & SQLite laufen.

Liebe Grüße,
Valentin

[Bummi]

da es vermutlich keine CURSOR oder Temporärtabellen mit (identity) unter MySQL gibts, keine Ahnung ob das unter MySQL geht, der SQL-Server verdaut es

Code:

update Tabelle set [order]=(Select Count(*) from (Select distinct top 10000000  [name] from tabelle order by [name] ) a where tabelle.[name] > a.[name]) + 1

[Iwo Asnet]

Sehr schöne Idee. Geht es einfacher?

Code:

update Tabelle 
  set [order]=(
            Select Count(*) 
              from tabelle x 
             where x.[name] > Tabelle.[name] 
             order by [name]
       )

(Ungetestet)

[Valle]

Ah, der Vergleich auf Größe zweier Varchars ist der Trick. Folgender Code tut seinen Zweck:

Code:

UPDATE `tabelle` SET `order` = (SELECT count(*) FROM (SELECT DISTINCT `name` FROM `tabelle` ORDER BY `name`) a WHERE `tabelle`.`name` > a.`name`);

Die "TOP 1000000" habe ich in ihrer Wirkung nicht wirklich verstanden. MySQL unterstützt die Syntax ohnehin nicht. Vermutlich ist es eine LIMIT Anweisung? Das "+ 1" am Ende macht das Query kompatibel zu meinem obigen Code, allerdings ist es für die Reihenfolge natürlich völlig unerheblich, ob man bei 0 oder 1 beginnt.

Vielen Dank!

@Iwo Asnet: ERROR 1093 (HY000): You can't specify target table 'tabelle' for update in FROM clause

Liebe Grüße,
Valentin

[shmia]

Zitat von Valle:

Ich habe eine Tabelle mit den Feldern Name und Order

1. Gebot der Datenbankprogrammierung
Du sollst in deinen Feld- und Tabellennamen keine reservierten Namen verwenden.
In Pascal/Delphi schreibst du ja auch nicht:

Delphi-Quellcode:

var
   False, True : Integer;  // Ja, der Compiler übersetzt das
begin
   for False := 1 to 10 do
      True := False;
end;

Man kann die Namen quoten, aber das Problem lauert auf dich und taucht dann auf, wenn du es überhaupt nicht brauchen kannst.

[Furtbichler]

Also in MSSQL geht das hier:

Code:

update tabelle set rank = (select count(*) from tabelle x where x.Name<tabelle.name)

[Valle]

Zitat von shmia:

Du sollst in deinen Feld- und Tabellennamen keine reservierten Namen verwenden.

Das Feld heißt weder wirklich order noch heißt die Tabelle tatsächlich tabelle.

Zitat von shmia:

In Pascal/Delphi schreibst du ja auch nicht:

Ich schreibe in Delphi / Pascal generell nicht.

Zitat von shmia:

Man kann die Namen quoten, aber das Problem lauert auf dich und taucht dann auf, wenn du es überhaupt nicht brauchen kannst.

Wir haben das bei einem recht umfangreichen Softwareprojekt in der Firma tatsächlich ein Mal gemacht. Bisher hatte ich nie Probleme damit, da unser ORM damit bestens klar kommt. Dennoch mache ich sowas, allein schon aus Gründen der Bequemlichkeit, nicht. In diesem Fall sollte nur klar gemacht werden, was die Spalte tut. Reihenfolge war mir für meine Tests zu lang.

@Furtbichler: Danke, aber das ist die gleiche Lösung wie die von Iwo Asnet. Entsprechend habe ich auch die selbe Fehlermeldung.

Es gibt noch ein kleines Manko an der Geschichte: Kommt der Inhalt der Name-Spalte mehrmals vor, erhalten logischerweise all diese Namen den gleichen Wert für Order. Das ist theoretisch nicht so schlimm, da hier automatisch sowieso keine Reihenfolge gefunden werden kann. Und theoretisch kommt das in diesem Fall auch nicht vor. Aber eigentlich ist das nicht besonders schön. Ich weiß auch nicht, was die Software, die diese Datenbank auswertet davon hält.

Liebe Grüße,
Valentin

[jobo]

MySql hat dieses schöne Variabeln Konstrukt, damit kann man sowas wie rownum generieren:

Code:

   
UPDATE tabelle u,
       (SELECT @rownum := @rownum + 1 ROWNUM,
               id
        FROM   (SELECT @rownum := 0) r,
               (SELECT id,
                       name
                FROM   tabelle
                ORDER  BY Name) s) x
SET    u.ordnung = x.rownum
WHERE  u.id = x.id

funktioniert nicht für ID mehrfach oder null

in sqlite gibts wohl nur rowid, vielleicht kann man das ähnlich einsetzen.

[Bummi]

Zitat:

Die "TOP 1000000" habe ich in ihrer Wirkung nicht wirklich verstanden

MSSQL hat ein Problem mit Order by in Subquerys, mit TOP xxx lässt es sich dazu überreden...