sql une Échanger des valeurs de colonne indexées uniques dans la base de données




sql server clé primaire auto increment (9)

J'ai une table de base de données et l'un des champs (pas la clé primaire) a un index unique dessus. Maintenant, je veux échanger des valeurs sous cette colonne pour deux lignes. Comment cela pourrait-il être fait? Deux hacks que je connais sont:

  1. Supprimer les deux lignes et les réinsérer
  2. Mettez à jour les lignes avec d'autres valeurs et échangez, puis mettez à jour la valeur réelle.

Mais je ne veux pas y aller car ils ne semblent pas être la solution appropriée au problème. Quelqu'un pourrait m'aider?


Oracle a différé la vérification d'intégrité qui résout exactement cela, mais il n'est pas disponible dans SQL Server ou MySQL.


Je pense également que # 2 est le meilleur pari, même si je serais sûr de l'envelopper dans une transaction au cas où quelque chose se passe mal à mi-mise à jour.

Une alternative (puisque vous avez demandé) à mettre à jour les valeurs de l'index unique avec différentes valeurs serait de mettre à jour toutes les autres valeurs dans les lignes à celle de l'autre rangée. Cela signifie que vous pouvez laisser les valeurs de l'index unique et, à la fin, vous obtenez les données souhaitées. Attention toutefois, dans le cas où une autre table référence cette table dans une relation de clé étrangère, toutes les relations dans la BD restent intactes.


Pour Oracle, il existe une option, DEFERRED, mais vous devez l'ajouter à votre contrainte.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Pour reporter TOUTES les contraintes reportables pendant toute la session, vous pouvez utiliser l'instruction ALTER SESSION SET constraints = DEFERRED.

La source


Le mot magique est DEFERRABLE ici:

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;

RÉSULTAT:

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)

En supposant que vous connaissiez le PK des deux lignes que vous voulez mettre à jour ... Cela fonctionne dans SQL Server, ne peut pas parler pour d'autres produits. SQL est (supposé être) atomique au niveau de l'instruction:

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;

donc vous allez partir de:

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

à:

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

Il existe une autre approche qui fonctionne avec SQL Server: utilisez une jointure de table temporaire dans votre instruction UPDATE.

Le problème est dû au fait que deux lignes ont la même valeur en même temps , mais si vous mettez à jour les deux lignes à la fois (à leurs nouvelles valeurs uniques), il n'y a pas de violation de contrainte.

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)

Merci à Rich H pour cette technique. - Marque


Je pense que vous devriez aller pour la solution 2. Il n'y a pas de fonction 'swap' dans toutes les variantes SQL que je connais.

Si vous devez le faire régulièrement, je suggère la solution 1, en fonction de la façon dont d'autres parties du logiciel utilisent ces données. Vous pouvez avoir des problèmes de verrouillage si vous ne faites pas attention.

Mais en un mot: il n'y a pas d'autre solution que celles que vous avez fournies.


Je pense généralement à une valeur qui n'a absolument aucun indice dans ma table pourrait avoir. Habituellement - pour des valeurs de colonnes uniques - c'est vraiment facile. Par exemple, pour les valeurs de la colonne 'position' (informations sur l'ordre de plusieurs éléments), c'est 0.

Vous pouvez ensuite copier la valeur A dans une variable, la mettre à jour avec la valeur B, puis définir la valeur B à partir de votre variable. Deux requêtes, je ne connais pas de meilleure solution.


Suite à la réponse d'Andy Irving

cela a fonctionné pour moi (sur SQL Server 2005) dans une situation similaire où j'ai une clé composite et j'ai besoin d'échanger un champ qui fait partie de la contrainte unique.

clé: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2

et j'ai besoin d'échanger LNUM pour que le résultat soit

clé: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0

le SQL nécessaire:

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






database