Tauschen Sie einzigartige indexierte Spalte Werte in der Datenbank

stimmen
47

Ich habe eine Datenbanktabelle und eines der Felder (nicht der Primärschlüssel) einen eindeutigen Index für es ist. Jetzt möchte ich Werte in dieser Spalte für zwei Reihen tauschen. Wie kann dies geschehen? Zwei Hacks ich kenne, sind:

  1. Löschen Sie beide Zeilen und legen Sie sie wieder
  2. Aktualisieren Sie Zeilen mit einem anderen Wert und Swap-und aktualisieren Sie dann auf den Istwert.

Aber ich will nicht für diese gehen, wie sie scheinen nicht die geeignete Lösung für das Problem zu sein. Kann mir jemand helfen?

Veröffentlicht am 03/08/2008 um 10:55
quelle vom benutzer
In anderen Sprachen...                            


12 antworten

stimmen
21

Das Zauberwort heißt DEFERRABLE hier:

DROP TABLE ztable CASCADE;
CREATE TABLE ztable
    ( id integer NOT NULL PRIMARY KEY
    , payload varchar
    );
INSERT IGNORE  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 IGNORE  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)
Beantwortet am 15/09/2012 um 13:38
quelle vom benutzer

stimmen
8

Ich glaube, Sie für Lösung gehen sollten 2. Es gibt keine ‚Swap‘ -Funktion in einer SQL-Variante die ich kenne.

Wenn Sie diese brauchen regelmäßig zu tun, schlage ich Lösung 1, je nachdem, wie andere Teile der Software diese Daten verwenden. Sie können Sperren Probleme haben, wenn du nicht aufpasst.

Aber kurz gesagt: es gibt keine andere Lösung, als die, die Sie zur Verfügung gestellt.

Beantwortet am 03/08/2008 um 13:26
quelle vom benutzer

stimmen
5

Es ist ein weiterer Ansatz, die mit SQL Server funktionieren: Verwenden Sie eine temporäre Tabelle, um es in Ihrer UPDATE-Anweisung beizutreten.

Das Problem wird durch zwei Reihen mit dem gleichen Wert verursacht zugleich , aber wenn Sie beide Zeilen aktualisieren auf einmal (um ihre neue, einzigartige Werte), gibt es keine Beschränkung verletzt.

Pseudo-Code:

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

Dank Rich-H für diese Technik. - Kennzeichen

Beantwortet am 04/04/2012 um 20:40
quelle vom benutzer

stimmen
5

Weitere Andy Irving Antwort

Dies funktionierte gut für mich (auf SQL Server 2005) in einer ähnlichen Situation, wo ich einen zusammengesetzten Schlüssel habe und ich brauche ein Feld zu tauschen, den Teil der eindeutigen Einschränkung ist.

Schlüssel: PID-, LNUM REC1: 10, 0 REC2: 10, 1 REC3: 10, 2

und ich brauche LNUM zu tauschen, so dass das Ergebnis

Schlüssel: PID-, LNUM REC1: 10, 1 REC2: 10, 2 REC3: 10, 0

die 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))
Beantwortet am 10/12/2008 um 13:19
quelle vom benutzer

stimmen
2

Ich habe das gleiche Problem. Hier ist meine vorgeschlagene Ansatz in PostgreSQL. In meinem Fall mein eindeutiger Index ist ein Sequenzwert, eine explizite Benutzer Ordnung auf meinen Zeilen zu definieren. Der Benutzer wird Reihen mischen um in einer web-app, dann die Änderungen vor.

Ich plane ein „vor“ Trigger hinzuzufügen. In diesem Trigger, wenn mein einzigartiger Indexwert aktualisiert wird, werde ich schauen, um zu sehen, ob irgendeine andere Zeile bereits meinen neuen Wert hält. Wenn ja, werde ich sie meinen alten Wert, geben und stehlen effektiv den Wert aus ihnen.

Ich hoffe, dass PostgreSQL wird mir erlauben, diese shuffle in dem vor Abzug zu tun.

Ich werde nach sich zurück und lassen Sie meine gebrauchte Autos wissen.

Beantwortet am 24/06/2009 um 04:58
quelle vom benutzer

stimmen
2

Angenommen, Sie kennen die PK der beiden Zeilen, die Sie aktualisieren möchten ... Dieses in SQL Server arbeitet, kann nicht für andere Produkte sprechen. SQL ist (sein soll) Atom auf Anweisungsebene:

CREATE TABLE testing
(
    cola int NOT NULL,
    colb CHAR(1) NOT NULL
);

CREATE UNIQUE INDEX UIX_testing_a ON testing(colb);

INSERT IGNORE  INTO testing VALUES (1, 'b');
INSERT IGNORE  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 dass Sie gehen von:

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

zu:

cola    colb
------------
1       a
2       b
Beantwortet am 16/09/2008 um 15:57
quelle vom benutzer

stimmen
2

Ich denke auch, dass # 2 ist die beste Wette, obwohl ich sicher sein, wäre es im Rahmen einer Transaktion, falls etwas schief geht zu wickeln Mitte Update.

Eine Alternative (da Sie danach gefragt werden) würde die eindeutige Index-Werte mit unterschiedlichen Werten zu aktualisieren sein, alle anderen Werte in den Reihen diejenigen der anderen Zeile zu aktualisieren. Dadurch bedeutet, dass Sie die eindeutigen Index-Werte allein lassen können, und am Ende, am Ende mit den Daten, die Sie wollen. Seien Sie vorsichtig, falls einige andere Tabellenverweise in dieser Tabelle in einer Fremdschlüsselbeziehung, dass alle Beziehungen in der DB erhalten bleiben.

Beantwortet am 03/08/2008 um 14:22
quelle vom benutzer

stimmen
1

1) schalten die IDs für Namen

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Für die Probeneingabe ist das Ausgangssignal:

id Student

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

„Im Fall n Anzahl der Zeilen, wie es schafft ......“

Beantwortet am 06/11/2018 um 08:56
quelle vom benutzer

stimmen
1

Für Oracle gibt es eine Option, ABGEGRENZTE, aber man muss es zu einem Zwang hinzuzufügen.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Um alle Einschränkungen zu verschieben, die während der gesamten Sitzung deferrable sind, können Sie die ALTE SESSION SET Constraints = ABGEGRENZTE Anweisung verwenden.

Quelle

Beantwortet am 27/04/2016 um 14:15
quelle vom benutzer

stimmen
1

In SQL Server kann die MERGE-Anweisung Zeilen aktualisieren, die normalerweise einen eindeutigen Schlüssel / INDEX brechen. (Nur soviel getestet, weil ich neugierig war.)

Allerdings würden Sie eine temporäre Tabelle / Variable zu liefern MERGE w / die notwendigen Zeilen verwenden.

Beantwortet am 20/04/2012 um 20:12
quelle vom benutzer

stimmen
1

Oracle hat die Integritätsprüfung verschoben, die genau diese lösen, aber es ist in SQL Server oder MySQL nicht verfügbar.

Beantwortet am 19/03/2010 um 20:29
quelle vom benutzer

stimmen
0

Ich denke, in der Regel von einem Wert, der absolut kein Index in meinem Tisch haben könnte. In der Regel - für eindeutige Spaltenwerte - es ist wirklich einfach. Zum Beispiel für die Werte von Spalte ‚Position‘ (Informationen über die Reihenfolge von mehreren Elementen) ist es 0 Punkte.

Dann können Sie Wert A auf eine Variable kopieren, aktualisieren Sie es mit dem Wert B und dann Sollwert B aus Ihren Variablen. Zwei Anfragen, ich kenne keine bessere Lösung though.

Beantwortet am 18/03/2017 um 18:00
quelle vom benutzer

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more