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

Translate

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

  1. Supprimez les deux lignes et réinsérez-les.
  2. Mettez à jour les lignes avec une autre valeur et échangez-les, 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?

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

Toutes les réponses

Translate

Je pense que vous devriez opter pour la solution 2. Il n'y a pas de fonction «swap» dans aucune variante SQL que je connaisse.

Si vous devez le faire régulièrement, je suggère la solution 1, en fonction de la manière 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 bref: il n'y a pas d'autre solution que celles que vous avez fournies.

La source
Translate

Le mot magique estDEFERRABLEici:

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

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))
La source
Translate

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à la fois, mais si vous mettez à jour les deux lignes en même temps (avec 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

La source
Translate

Je pense également que le n ° 2 est le meilleur pari, même si je serais sûr de l'envelopper dans une transaction en cas de problème à mi-mise à jour.

Une alternative (puisque vous l'avez demandé) à la mise à jour des valeurs d'index unique avec des valeurs différentes serait de mettre à jour toutes les autres valeurs des lignes vers celles de l'autre ligne. Cela signifie que vous pouvez laisser les valeurs d'index unique seules, et à la fin, vous vous retrouvez avec les données que vous voulez. Attention cependant, au cas où une autre table ferait référence à cette table dans une relation de clé étrangère, que toutes les relations dans la base de données restent intactes.

La source
Translate

En supposant que vous connaissez le PK des deux lignes que vous souhaitez mettre à jour ... Cela fonctionne dans SQL Server, je ne peux 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;

vous irez donc de:

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

à:

cola    colb
------------
1       a
2       b
La source
Translate

J'ai le même problème. Voici ma proposition d'approche dans PostgreSQL. Dans mon cas, mon index unique est une valeur de séquence, définissant un ordre utilisateur explicite sur mes lignes. L'utilisateur va mélanger les lignes dans une application Web, puis soumettre les modifications.

Je prévois d'ajouter un déclencheur "avant". Dans ce déclencheur, chaque fois que ma valeur d'index unique est mise à jour, je vais regarder si une autre ligne contient déjà ma nouvelle valeur. Si tel est le cas, je leur donnerai mon ancienne valeur et je leur volerai effectivement la valeur.

J'espère que PostgreSQL me permettra de faire ce shuffle dans le déclencheur before.

Je posterai et vous informerai de mon kilométrage.

La source
Translate

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

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Pour différer TOUTES les contraintes qui peuvent être différées pendant toute la session, vous pouvez utiliser l'instruction ALTER SESSION SET contraintes = DEFERRED.

La source

La source
Lis
Translate

Je pense généralement à une valeur qu'aucun index de ma table ne pourrait avoir. Habituellement, pour les valeurs de colonne uniques, c'est vraiment facile. Par exemple, pour les valeurs de la colonne 'position' (informations sur l'ordre de plusieurs éléments), la valeur est 0.

Ensuite, vous pouvez 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 questions, je ne connais pas de meilleure solution cependant.

La source
Translate

Oracle a différé la vérification de l'intégrité qui résout exactement ce problème, mais elle n'est disponible ni dans SQL Server ni dans MySQL.

La source
Translate

Dans SQL Server, l'instruction MERGE peut mettre à jour des lignes qui normalement casseraient une clé / indice UNIQUE. (Je viens de tester cela parce que j'étais curieux.)

Cependant, vous devrez utiliser une table / variable temporaire pour fournir MERGE avec les lignes nécessaires.

La source
Translate

1) Changer les identifiants pour le nom

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Pour l'entrée échantillon, la sortie est:

identifiant étudiant

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

"dans le cas n nombre de lignes comment va gérer ......"

La source