webentwicklung-frage-antwort-db.com.de

UPDATE mit ORDER BY

Sie müssen UPDATE mit ORDER BY "binden". Ich versuche, Cursor zu verwenden, bekomme aber den Fehler:

cursor "cursupd" doesn't specify a line,
SQL state: 24000

Code:

BEGIN;
    DECLARE cursUpd CURSOR FOR SELECT * FROM "table" WHERE "field" = 5760 AND "sequence" >= 0 AND "sequence" < 9 ORDER BY "sequence" DESC;
    UPDATE "table" SET "sequence" = "sequence" + 2 WHERE CURRENT OF cursUpd;
    CLOSE cursUpd;
COMMIT;

Wie mache ich das richtig?

UPDATE 1

Ohne Cursor, wenn mir das gefällt:

UPDATE "CableLinePoint" AS "t"
SET "sequence" = t."sequence" + 2
from (
    select max("sequence") "sequence", "id"
    from "CableLinePoint"
    where
        "CableLine" = 5760
    group by "id"
    ORDER BY "sequence" DESC
) "s"
where "t"."id" = "s"."id" and "t"."sequence" = "s"."sequence"

Ich bekomme den eindeutigen Fehler. Daher müssen Sie von Anfang an aktualisieren und nicht von Anfang an.

UPDATE 2

Tabelle:

id|CableLine|sequence
10|    2    |    1
11|    2    |    2
12|    2    |    3
13|    2    |    4
14|    2    |    5

Das Feld "Reihenfolge" muss aktualisiert (erhöht) werden. "Sequenz" hat "Index" -Typ, daher kann nicht ausgeführt werden:

UPDATE "table" SET "sequence" = "sequence" + 1 WHERE "CableLine" = 2

Wenn "Sequenz" in der Zeile mit id = 10 um 1 erhöht wird, erhalte ich die Fehlermeldung, dass bereits eine andere Zeile mit "sequence" = 2 vorhanden ist.

sqlpostgresqlsql-updatesql-order-by
11
dedoki

UPDATE mit ORDER BY

Zu der Frage, die angesprochen wurde, lautet der Titel: Es gibt keinen ORDER BY in einem SQL-Befehl UPDATE. Postgres aktualisiert Zeilen in beliebiger Reihenfolge. Sie haben jedoch (begrenzte) Optionen, um zu entscheiden, ob die Einschränkungen nach jeder Zeile, nach jeder Anweisung oder am Ende der Transaktion geprüft werden. Sie können doppelte Schlüsselverletzungen für Intermediate-Zustände mit einer DEFERRABLE-Einschränkung vermeiden.

Ich zitiere, was wir unter dieser Frage herausgearbeitet haben:
Einschränkung definiert DEFERRABLE INITIALLY SOFORT ist immer noch DEFERRED?

  • NOT DEFERRED-Bedingungen werden geprüft nach jeder Zeile.

  • DEFERRABLE-Einschränkungen, die auf IMMEDIATE (INITIALLY IMMEDIATE oder über SET CONSTRAINTS) gesetzt sind, werden geprüft nach jeder Anweisung.

Es gibt jedoch Einschränkungen. Fremdschlüsseleinschränkungen erfordern nicht widerrufbar -Einschränkungen für die Zielspalte (n).

Die Spalten, auf die verwiesen wird, müssen die Spalten einer nicht zu überschreibenden eindeutigen - oder Primärschlüsseleinschränkung in der referenzierten Tabelle sein.

Workaround

Nach dem Update der Frage aktualisiert.
Wenn "sequence" im normalen Betrieb niemals negativ ist, können Sie eindeutige Fehler wie folgt vermeiden:

UPDATE tbl SET "sequence" = ("sequence" + 1) * -1
WHERE  "CableLine" = 2;

UPDATE tbl SET "sequence" = "sequence" * -1
WHERE  "CableLine" = 2
AND    "sequence" < 0;

Bei einer nicht aufschiebbaren Einschränkung (Standard) müssen Sie zwei separate Transaktionen ausführen, damit dies funktioniert. Führen Sie die Befehle nacheinander aus, um Parallelitätsprobleme zu vermeiden. Die Lösung ist offensichtlich nicht für starke gleichzeitige Belastung geeignet.

Beiseite:
Es ist in Ordnung, das Schlüsselwort AS für Tabellenaliase zu überspringen, es wird jedoch davon abgeraten, dasselbe für Spaltenaliasnamen zu tun.

Ich würde raten, keine SQL-Schlüsselwörter als Bezeichner zu verwenden, obwohl dies erlaubt ist.

Vermeiden Sie das Problem

In einem größeren Maßstab oder bei Datenbanken mit starker gleichzeitiger Last ist es ratsam, eine serial -Spalte für die relative Reihenfolge der Zeilen zu verwenden. Sie können mit der Fensterfunktion row_number() in einer Ansicht oder Abfrage Zahlen beginnend mit 1 und ohne Lücken generieren. Betrachten Sie diese verwandte Antwort:
Ist es möglich, eine PG-Sequenz auf einem Label pro Datensatz zu verwenden?

9
Erwin Brandstetter

UPDATE mit ORDER BY:

UPDATE thetable 
  SET columntoupdate=yourvalue 
 FROM (SELECT rowid, 'thevalue' AS yourvalue 
         FROM thetable 
        ORDER BY rowid
      ) AS t1 
WHERE thetable.rowid=t1.rowid;

Die Reihenfolge von UPDATE ist immer noch zufällig (ich denke mal), aber die an den Befehl UPDATE gelieferten Werte werden durch die thetable.rowid=t1.rowid-Bedingung abgeglichen. Was ich also tue, ist, zuerst die 'aktualisierte' Tabelle im Speicher auszuwählen, im obigen Code t1 zu benennen, und dann meine physikalische Tabelle so aussehen zu lassen wie t1. Und die Update-Reihenfolge spielt keine Rolle mehr.

Was wahres bestelltes UPDATE betrifft, glaube ich nicht, dass es für jedermann nützlich sein könnte.

12
alexkovelsky
Update with Order By

Declare 
v number;
cursor c1 is 
    Select col2 from table1 order by col2;
    begin
    v:=0;
     for c in c1
     loop
    update table1 
    set col1 =v+1
    where col2 = c.col2;
    end loop;
    commit;
    END;
1
Syed Najam ul Hassan

Das hat für mich funktioniert:

[update-Anweisung hier] OPTION (MAXDOP 1) - Verhindert, dass die Zeilengröße eine eifrige Spule verwendet, wodurch die Reihenfolge der Datensätze verändert wird.

Ich verwende einen gruppierten int-Index in sequentieller Reihenfolge (erzeuge bei Bedarf einen) und hatte bis vor kurzem kein Problem, und selbst dann nur bei kleinen Rowsets, auf die sich der Abfrageplan-Optimierer (ungewollt) für eine Lazy-Spool entschieden hat.

Theoretisch könnte ich die neue Option verwenden, um die Spool-Verwendung zu verbieten, aber ich finde maxdop einfacher.

Ich bin in einer einzigartigen Situation, da die Berechnungen isoliert sind (Einzelbenutzer). Eine andere Situation erfordert möglicherweise eine Alternative zur Verwendung des maxdop-Grenzwerts, um Konflikte zu vermeiden.

0
cmore

Lazy Way , (aka nicht der schnellste oder beste Weg)

CREATE OR REPLACE FUNCTION row_number(table_name text, update_column text, start_value integer, offset_value integer, order_by_column text, order_by_descending boolean)
  RETURNS void AS
$BODY$
DECLARE
    total_value integer;
    my_id text;
    command text;
BEGIN
total_value = start_value;
    command = 'SELECT ' || order_by_column || ' FROM ' || table_name || ' ORDER BY '  || order_by_column;

    if (order_by_descending) THEN
        command = command || ' desc';
    END IF;

    FOR  my_id in  EXECUTE command LOOP
        command = 'UPDATE ' || table_name || ' SET  ' || update_column || ' = ' || total_value || ' WHERE ' || order_by_column || ' = ' ||  my_id|| ';';

        EXECUTE command;
        total_value = total_value + offset_value;
    END LOOP;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Beispiel

SELECT Zeilennummer ('regispro_spatial_2010.ags_states_spatial', 'order_id', 10,1, 'ogc_fid', true)

0
user3605589