Swap valeurs de colonnes indexées uniques dans la base de données

voix
47

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

  1. Supprimer les lignes et les réinsérer
  2. Mettre à jour les lignes avec une autre valeur et d'échange, puis mettre à jour à la valeur réelle.

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

Créé 03/08/2008 à 10:55
source utilisateur
Dans d'autres langues...                            


11 réponses

voix
21

Le mot magique est DEFERRABLE ici:

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;

RÉSULTAT:

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)
Créé 15/09/2012 à 13:38
source utilisateur

voix
8

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

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

Créé 03/08/2008 à 13:26
source utilisateur

voix
5

Il y a une autre approche qui fonctionne avec SQL Server: utilisez se joindre à une table temporaire pour dans votre instruction UPDATE.

Le problème est dû à la présence de deux lignes avec 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 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

Créé 04/04/2012 à 20:40
source utilisateur

voix
5

Suite à la réponse de 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é: NID, LNUM REC1: 10, 0 REC2: 10, 1 REC3: 10, 2

et je dois échanger LNUM afin que le résultat est

clé: NID, LNUM REC1: 10, 1 REC2: 10, 2 REC3: 10, 0

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))
Créé 10/12/2008 à 13:19
source utilisateur

voix
2

J'ai le même problème. Voici mon approche proposée dans PostgreSQL. Dans mon cas, mon index unique est une valeur de séquence, la définition d'un ordre explicite de l'utilisateur sur mes lignes. L'utilisateur mélanger les lignes autour d'une web-app, puis soumettre les modifications.

Je prévois d'ajouter un « avant » déclencheur. Dans ce déclencheur, chaque fois que ma valeur d'index unique est mis à jour, je vais regarder pour voir si une autre ligne détient déjà ma nouvelle valeur. Si oui, je leur donnerai ma vieille valeur, et de voler efficacement la valeur de leur.

J'espère que PostgreSQL va me permettre de faire ce remaniement dans le déclencheur avant.

Je vais republier et vous faire connaître mon kilométrage.

Créé 24/06/2009 à 04:58
source utilisateur

voix
2

En supposant que vous connaissez le PK des deux lignes que vous souhaitez mettre à jour ... Cela fonctionne dans SQL Server, ne peut pas parler pour les autres produits. SQL est (censé ê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 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;

donc vous passer de:

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

à:

cola    colb
------------
1       a
2       b
Créé 16/09/2008 à 15:57
source utilisateur

voix
2

Je pense aussi que # 2 est le meilleur pari, bien que je serais sûr de l'envelopper dans une transaction dans le cas où quelque chose va mal mi-mise à jour.

Une alternative (puisque vous avez demandé) à mettre à jour les valeurs de l'indice unique avec des valeurs différentes serait de mettre à jour toutes les autres valeurs dans les lignes à celle de l'autre rangée. Faire cela signifie que vous pouvez laisser les valeurs de l'indice unique seul, et à la fin, vous vous retrouvez avec les données que vous voulez. Attention cependant, dans le cas d'autres références de la table cette table dans une relation de clé étrangère, que toutes les relations dans la base de données restent intactes.

Créé 03/08/2008 à 14:22
source utilisateur

voix
1

Pour Oracle il y a une option, Différé, mais vous devez l'ajouter à votre contrainte.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

De reporter toutes les contraintes qui sont reportables pendant toute la session, vous pouvez utiliser les contraintes SET SESSION = instruction ALTER REPORTÉ.

La source

Créé 27/04/2016 à 14:15
source utilisateur

voix
1

Dans SQL Server, l'instruction MERGE peut mettre à jour les lignes qui, en temps normal KEY / INDEX UNIQUE. (Juste testé parce que je suis curieux.)

Cependant, il faudrait utiliser une table temporaire / variable pour fournir MERGE w / les lignes nécessaires.

Créé 20/04/2012 à 20:12
source utilisateur

voix
1

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.

Créé 19/03/2010 à 20:29
source utilisateur

voix
0

Je pense généralement d'une valeur absolument aucun indice dans ma table pourrait avoir. En général, - pour les valeurs de colonne unique - il est vraiment facile. Par exemple, pour les valeurs de la colonne « position » (informations sur l'ordre de plusieurs éléments), il est 0.

Ensuite, vous pouvez copier la valeur A à une variable, mettre à jour avec la valeur B et valeur de consigne B de votre variable. Deux requêtes, je sais pas de meilleure solution si.

Créé 18/03/2017 à 18:00
source utilisateur

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