Tauschen Sie eindeutige indizierte Spaltenwerte in der Datenbank aus

  • Ich habe eine Datenbanktabelle und eines der Felder (nicht der Primärschlüssel) verfügt über einen eindeutigen Index. Jetzt möchte ich Werte in dieser Spalte für zwei Zeilen austauschen. Wie kann das gemacht werden? Ich kenne zwei Hacks:

    1. Beide Zeilen löschen und erneut einfügen
    2. Zeilen mit einem anderen Wert aktualisieren und tauschen und dann auf den tatsächlichen Wert aktualisieren.

    Aber ich möchte nicht auf diese Werte eingehen, da sie nicht als angemessen erscheinen Lösung für das Problem. Könnte mir jemand helfen?

    25 April 2012
    Charles MenguyBrian DeRocher
11 answers
  • Das Zauberwort lautet DEFERRABLE hier:

     DROP TABLE ztable CASCADE;
    CREATE TABLE ztable
        ( id integer NOT NULL PRIMARY KEY
        , payload varchar
        );
    INSERT INTO ztable(id,payload) VALUES (1,'one' ), (2,'two' ), (3,'three' );
    SELECT * FROM ztable;
    
    
        -- This works, because there is no constraint
    UPDATE ztable t1
    SET payload=t2.payload
    FROM ztable t2
    WHERE t1.id IN (2,3)
    AND t2.id IN (2,3)
    AND t1.id <> t2.id
        ;
    SELECT * FROM ztable;
    
    ALTER TABLE ztable ADD CONSTRAINT OMG_WTF UNIQUE (payload)
        DEFERRABLE INITIALLY DEFERRED
        ;
    
        -- This should also work, because the constraint 
        -- is deferred until "commit time"
    UPDATE ztable t1
    SET payload=t2.payload
    FROM ztable t2
    WHERE t1.id IN (2,3)
    AND t2.id IN (2,3)
    AND t1.id <> t2.id
        ;
    SELECT * FROM ztable;
     

    ERGEBNIS :

     DROP TABLE
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ztable_pkey" for table "ztable"
    CREATE TABLE
    INSERT 0 3
     id | payload
    ----+---------
      1 | one
      2 | two
      3 | three
    (3 rows)
    
    UPDATE 2
     id | payload
    ----+---------
      1 | one
      2 | three
      3 | two
    (3 rows)
    
    NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "omg_wtf" for table "ztable"
    ALTER TABLE
    UPDATE 2
     id | payload
    ----+---------
      1 | one
      2 | two
      3 | three
    (3 rows)
     
    15 September 2012
    wildplasser
  • Ich denke, Sie sollten sich für Lösung 2 entscheiden. Es gibt in keiner SQL-Variante, die ich kenne, eine 'Swap'-Funktion.

    Wenn Sie dies regelmäßig tun müssen Ich empfehle Lösung 1, je nachdem, wie andere Teile der Software diese Daten verwenden. Sie können Sperrprobleme haben, wenn Sie nicht aufpassen.

    Kurz gesagt: Es gibt keine andere Lösung als die, die Sie angegeben haben.

    03 August 2008
    Daan
  • Neben der Antwort von Andy Irving

    funktionierte dies für mich (auf SQL Server 2005) in einer ähnlichen Situation, in der ich einen zusammengesetzten Schlüssel habe und brauche tauschen Sie ein Feld aus, das Teil der einzigartigen Einschränkung ist.

    -Taste: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3 : 10, 2

    und ich muss LNUM austauschen, sodass das Ergebnis

    ist: pID, LNUM rec1 : 10, 1 rec2: 10, 2 rec3: 10, 0

    die erforderliche SQL:

     UPDATE    DOCDATA    
    SET       LNUM = CASE LNUM
                  WHEN 0 THEN 1
                  WHEN 1 THEN 2 
                  WHEN 2 THEN 0 
              END
    WHERE     (pID = 10) 
      AND     (LNUM IN (0, 1, 2))
     
    10 December 2008
  • Es gibt einen anderen Ansatz, der mit SQL Server funktioniert: Verwenden Sie eine temporäre Tabellenverknüpfung dazu in Ihrer UPDATE-Anweisung.

    Das Problem wird durch zwei Zeilen mit verursacht derselbe Wert zur gleichen Zeit , aber wenn Sie beide Zeilen gleichzeitig aktualisieren (auf ihre neuen, eindeutigen Werte), liegt keine Einschränkungsverletzung vor.

    Pseudocode:

     -- setup initial data values:
    insert into data_table(id, name) values(1, 'A')
    insert into data_table(id, name) values(2, 'B')
    
    -- create temp table that matches live table
    select top 0 * into #tmp_data_table from data_table
    
    -- insert records to be swapped
    insert into #tmp_data_table(id, name) values(1, 'B')
    insert into #tmp_data_table(id, name) values(2, 'A')
    
    -- update both rows at once! No index violations!
    update data_table set name = #tmp_data_table.name
    from data_table join #tmp_data_table on (data_table.id = #tmp_data_table.id)
     

    Vielen Dank an Rich H für diese Technik. - Markieren Sie

    04 April 2012
    Mark Gaulin
  • Ich denke auch, dass # 2 die beste Wette ist, obwohl ich sicher wäre, es in eine Transaktion einzuwickeln, falls mitten im Update etwas schief geht.

    Eine Alternative (da Sie gefragt wurden), um die eindeutigen Indexwerte mit anderen Werten zu aktualisieren, besteht darin, alle anderen Werte in den Zeilen auf die der anderen Zeile zu aktualisieren. Dies bedeutet, dass Sie die Unique-Index-Werte alleine lassen können und am Ende die gewünschten Daten erhalten. Falls jedoch eine andere Tabelle auf diese Tabelle in einer Fremdschlüsselbeziehung verweist, ist zu beachten, dass alle Beziehungen in der Datenbank erhalten bleiben.

    03 August 2008
    Yaakov Ellis
  • Ich habe das gleiche Problem. Hier ist mein vorgeschlagener Ansatz in PostgreSQL. In meinem Fall ist mein eindeutiger Index ein Sequenzwert, der eine explizite Benutzerreihenfolge in meinen Zeilen definiert. Der Benutzer mischt die Zeilen in einer Web-App um und sendet dann die Änderungen.

    Ich habe vor, einen "Vorher" -Auslöser hinzuzufügen. Immer wenn mein eindeutiger Indexwert aktualisiert wird, prüfe ich in diesem Trigger, ob eine andere Zeile bereits meinen neuen Wert enthält. Wenn ja, gebe ich ihnen meinen alten Wert und stiehlt ihnen effektiv den Wert.

    Ich hoffe, dass PostgreSQL mir erlaubt, diesen Shuffle im vorherigen Trigger auszuführen.

    Ich poste zurück und gebe Ihnen Auskunft über meine Laufleistung.

    24 June 2009
    the.jxc
  • Angenommen, Sie kennen den PK der zwei Zeilen, die Sie aktualisieren möchten ... Dies funktioniert in SQL Server und kann nicht für andere Produkte sprechen. SQL ist auf Anweisungsebene (soll) atomar sein:

     CREATE TABLE testing
    (
        cola int NOT NULL,
        colb CHAR(1) NOT NULL
    );
    
    CREATE UNIQUE INDEX UIX_testing_a ON testing(colb);
    
    INSERT INTO testing VALUES (1, 'b');
    INSERT INTO testing VALUES (2, 'a');
    
    SELECT * FROM testing;
    
    UPDATE testing
    SET colb = CASE cola WHEN 1 THEN 'a'
                    WHEN 2 THEN 'b'
                    END
    WHERE cola IN (1,2);
    
    SELECT * FROM testing;
     

    Sie gehen also aus:

     cola    colb
    ------------
    1       b
    2       a
     

    an:

     cola    colb
    ------------
    1       a
    2       b
     
    18 April 2016
    takrlKhal
  • Für Oracle gibt es die Option DEFERRED, die Sie jedoch Ihrer Einschränkung hinzufügen müssen.

     SET CONSTRAINT emp_no_fk_par DEFERRED; 
     

    Um ALLE Einschränkungen zu verschieben, die während der gesamten Sitzung verschoben werden können, können Sie die Anweisung ALTER SESSION SET constraints = DEFERRED verwenden.

    Quelle

    27 April 2016
    TheBakker
  • In SQL Server kann die MERGE-Anweisung Zeilen aktualisieren, die normalerweise einen UNIQUE KEY / INDEX beschädigen würden. (Ich habe das gerade getestet, weil ich neugierig war.)

    Allerdings müssten Sie eine temporäre Tabelle / Variable verwenden, um MERGE mit den erforderlichen Zeilen bereitzustellen |>

    20 April 2012
    Ben Mosher
  • Oracle hat die Integritätsprüfung verzögert, wodurch genau dies gelöst wird, sie ist jedoch weder in SQL Server noch in MySQL verfügbar.

    19 March 2010
    BlueRaja - Danny Pflughoeft