Scambia i valori delle colonne indicizzate unici in banca dati

voti
47

Ho una tabella del database e uno dei campi (non la chiave primaria) sta avendo un indice univoco su di esso. Ora voglio scambiare i valori in questa colonna per due righe. Come potrebbe questo essere fatto? Due hack che conosco sono:

  1. Eliminare entrambe le righe e reinserirle
  2. Aggiornare righe con qualche altro valore e swap e quindi aggiornare al valore reale.

Ma io non voglio andare per questi come non sembrano essere la soluzione adeguata al problema. Qualcuno può aiutarmi?

È pubblicato 03/08/2008 alle 10:55
fonte dall'utente
In altre lingue...                            


12 risposte

voti
21

La parola magica è DEFERRABLE qui:

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;

RISULTATO:

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)
Risposto il 15/09/2012 a 13:38
fonte dall'utente

voti
8

Penso che si dovrebbe andare per soluzione 2. Non esiste una funzione 'scambio' in qualsiasi variante di SQL io conosca.

Se avete bisogno di farlo regolarmente, suggerisco la soluzione 1, a seconda di come altre parti del software stanno utilizzando questi dati. Si possono avere problemi di blocco, se non stai attento.

Ma insomma: non c'è altra soluzione che quelli che hai fornito.

Risposto il 03/08/2008 a 13:26
fonte dall'utente

voti
5

C'è un altro approccio che funziona con SQL Server: utilizzare una tabella temporanea unirsi ad esso nella sua dichiarazione UPDATE.

Il problema è causato da avere due file con lo stesso valore , allo stesso tempo , ma se si aggiorna entrambe le file in una sola volta (per i loro nuovi, valori unici), non v'è alcuna violazione del vincolo.

Pseudo-codice:

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

Grazie a Rich H per questa tecnica. - Marchio

Risposto il 04/04/2012 a 20:40
fonte dall'utente

voti
5

A seguito della risposta di Andy Irving

questo ha funzionato per me (su SQL Server 2005) in una situazione simile in cui ho una chiave composita e ho bisogno di scambiare un campo che fa parte del vincolo univoco.

chiave: PID, rec1 lNum: 10, 0 rec2: 10, 1 REC3: 10, 2

e ho bisogno di scambiare lnum in modo che il risultato sia

chiave: PID, rec1 lNum: 10, 1 rec2: 10, 2 REC3: 10, 0

SQL aveva bisogno:

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))
Risposto il 10/12/2008 a 13:19
fonte dall'utente

voti
2

Ho lo stesso problema. Ecco il mio approccio proposto in PostgreSQL. Nel mio caso, il mio indice univoco è un valore sequenza, definente un utente-ordine esplicito sulle righe. L'utente provvederà a mischiare le righe in giro in una web-app, quindi inviare le modifiche.

Sto pensando di aggiungere un "prima" grilletto. In quel grilletto, ogni volta che il mio valore indice univoco viene aggiornato, cercherò di vedere se qualche altra riga detiene già il mio nuovo valore. Se è così, io darò loro il mio vecchio valore, e rubare in modo efficace il valore fuori di loro.

Spero che PostgreSQL mi permetterà di fare questo riordino nel trigger prima.

Vi posto di nuovo e ti faccio sapere la mia distanza in miglia.

Risposto il 24/06/2009 a 04:58
fonte dall'utente

voti
2

Supponendo che si conosce il PK delle due file che si desidera aggiornare ... questo funziona in SQL Server, non posso parlare per gli altri prodotti. SQL è (dovrebbe essere) atomica a livello di istruzione:

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;

in modo da andare da:

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

a:

cola    colb
------------
1       a
2       b
Risposto il 16/09/2008 a 15:57
fonte dall'utente

voti
2

Penso anche che # 2 è la migliore scommessa, anche se mi piacerebbe essere sicuri di avvolgerlo in una transazione in caso qualcosa vada metà aggiornamento sbagliato.

Un'alternativa (dal momento che ha chiesto) per l'aggiornamento dei valori di indice univoco con valori diversi sarebbe quello di aggiornare tutti gli altri valori nelle righe a quella dell'altro fila. Fare questo significa che è possibile lasciare i valori indice univoco da solo, e alla fine, si finisce con i dati che si desidera. Attenzione però, nel caso in cui alcuni altri riferimenti tavolo questa tabella in una relazione di chiave esterna, che tutte le relazioni nel DB rimangono intatti.

Risposto il 03/08/2008 a 14:22
fonte dall'utente

voti
1

1) passare gli ID per nome

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Per l'ingresso del campione, il risultato è:

studente id

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

"Nel caso in cui il numero n di righe come gestirà ......"

Risposto il 06/11/2018 a 08:56
fonte dall'utente

voti
1

Per Oracle non è un'opzione, in differita, ma bisogna aggiungerlo al vincolo.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Di rinviare tutti i vincoli che sono indifferibile durante l'intera sessione, è possibile utilizzare l'ALTER SESSION SET vincoli = istruzione differita.

fonte

Risposto il 27/04/2016 a 14:15
fonte dall'utente

voti
1

In SQL Server, l'istruzione MERGE può aggiornare le righe che normalmente spezzare una chiave univoca / INDEX. (Appena testato questo perché ero curioso.)

Tuttavia, dovreste usare una temperatura tavolo / variabile per fornire MERGE w / le righe necessarie.

Risposto il 20/04/2012 a 20:12
fonte dall'utente

voti
1

Oracle ha differito controllo di integrità che risolve esattamente questo, ma non è disponibile in SQL Server o MySQL.

Risposto il 19/03/2010 a 20:29
fonte dall'utente

voti
0

Io di solito penso a un valore che assolutamente nessun indice nella mia tabella potrebbe avere. Di solito - per i valori di colonna univoci - è davvero facile. Ad esempio, per valori di colonna 'posizione' (informazioni sull'ordine di più elementi) è 0.

Quindi è possibile copiare il valore A una variabile, aggiornarlo con valore di B e il valore quindi impostare B dalla variabile. Due domande, io non conoscono soluzione migliore però.

Risposto il 18/03/2017 a 18:00
fonte dall'utente

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