sql - Tauschen Sie eindeutige indizierte Spaltenwerte in der Datenbank aus

Translate

Ich habe eine Datenbanktabelle und eines der Felder (nicht der Primärschlüssel) hat einen eindeutigen Index. Jetzt möchte ich die Werte unter dieser Spalte gegen zwei Zeilen austauschen. Wie könnte das gemacht werden? Zwei Hacks, die ich kenne, sind:

  1. Löschen Sie beide Zeilen und fügen Sie sie erneut ein.
  2. Aktualisieren Sie die Zeilen mit einem anderen Wert und tauschen Sie sie aus und aktualisieren Sie sie dann auf den tatsächlichen Wert.

Aber ich möchte mich nicht für diese entscheiden, da sie nicht die geeignete Lösung für das Problem zu sein scheinen. Könnte mir jemand helfen?

This question and all comments follow the "Attribution Required."

Alle Antworten

Translate

Ich denke, Sie sollten sich für Lösung 2 entscheiden. In keiner mir bekannten SQL-Variante gibt es eine Swap-Funktion.

Wenn Sie dies regelmäßig tun müssen, schlage ich Lösung 1 vor, je nachdem, wie andere Teile der Software diese Daten verwenden. Sie können Probleme beim Sperren haben, wenn Sie nicht vorsichtig sind.

Aber kurz gesagt: Es gibt keine andere Lösung als die, die Sie bereitgestellt haben.

Quelle
Translate

Das Zauberwort istDEFERRABLEHier:

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)
Quelle
Corey Lee
Translate

Weiter zu Andy Irvings Antwort

Dies funktionierte für mich (unter SQL Server 2005) in einer ähnlichen Situation, in der ich einen zusammengesetzten Schlüssel habe und ein Feld austauschen muss, das Teil der eindeutigen Einschränkung ist.

Schlüssel: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2

und ich muss LNUM tauschen, damit das Ergebnis ist

Schlüssel: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0

das SQL benötigt:

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))
Quelle
Translate

Es gibt einen anderen Ansatz, der mit SQL Server funktioniert: Verwenden Sie einen temporären Tabellen-Join in Ihrer UPDATE-Anweisung.

Das Problem wird durch zwei Zeilen mit demselben Wert verursachtgleichzeitigWenn Sie jedoch 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. - Kennzeichen

Quelle
Translate

Ich denke auch, dass # 2 die beste Wahl ist, obwohl ich es sicher in eine Transaktion einwickeln würde, falls während des Updates etwas schief geht.

Eine Alternative (seit Sie gefragt haben) zum Aktualisieren der eindeutigen Indexwerte mit unterschiedlichen Werten besteht darin, alle anderen Werte in den Zeilen auf die der anderen Zeile zu aktualisieren. Wenn Sie dies tun, können Sie die Werte für den eindeutigen Index in Ruhe lassen und erhalten am Ende die gewünschten Daten. Seien Sie jedoch vorsichtig, falls alle anderen Tabellen in einer Fremdschlüsselbeziehung auf diese Tabelle verweisen, dass alle Beziehungen in der Datenbank intakt bleiben.

Quelle
Translate

Angenommen, Sie kennen die PK der beiden Zeilen, die Sie aktualisieren möchten ... Dies funktioniert in SQL Server und kann nicht für andere Produkte sprechen. SQL ist auf Anweisungsebene atomar (soll es 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;

so wirst du gehen von:

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

zu:

cola    colb
------------
1       a
2       b
Quelle
Translate

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 für meine Zeilen definiert. Der Benutzer mischt Zeilen in einer Web-App und übermittelt dann die Änderungen.

Ich plane, einen "Vorher" -Trigger hinzuzufügen. In diesem Trigger werde ich bei jeder Aktualisierung meines eindeutigen Indexwerts prüfen, ob eine andere Zeile bereits meinen neuen Wert enthält. Wenn ja, werde ich ihnen meinen alten Wert geben und ihnen effektiv den Wert stehlen.

Ich hoffe, dass PostgreSQL es mir ermöglicht, dieses Shuffle im Vorher-Trigger durchzuführen.

Ich werde zurückschicken und Ihnen meinen Kilometerstand mitteilen.

Quelle
Translate

Für Oracle gibt es die Option DEFERRED, die Sie jedoch zu 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 boundes = DEFERRED verwenden.

Quelle

Quelle
Lis
Translate

Normalerweise denke ich an einen Wert, den absolut kein Index in meiner Tabelle haben könnte. Normalerweise ist es für eindeutige Spaltenwerte sehr einfach. Für Werte der Spalte 'Position' (Informationen zur Reihenfolge mehrerer Elemente) ist sie beispielsweise 0.

Anschließend können Sie Wert A in eine Variable kopieren, mit Wert B aktualisieren und dann Wert B aus Ihrer Variablen festlegen. Zwei Fragen, ich kenne allerdings keine bessere Lösung.

Quelle
Translate

Oracle hat die Integritätsprüfung verschoben, wodurch genau dies gelöst wird. Sie ist jedoch weder in SQL Server noch in MySQL verfügbar.

Quelle
Translate

In SQL Server kann die MERGE-Anweisung Zeilen aktualisieren, die normalerweise einen EINZIGARTIGEN SCHLÜSSEL / INDEX beschädigen würden. (Habe das gerade getestet, weil ich neugierig war.)

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

Quelle
Translate

1) Wechseln Sie die IDs für den Namen

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Für die Probeneingabe lautet die Ausgabe:

ID Student

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

"falls n Anzahl von Zeilen wie verwaltet werden ......"

Quelle