sql - Scambia valori di colonna indicizzati univoci nel database

Translate

Ho una tabella di database e uno dei campi (non la chiave primaria) ha un indice univoco su di esso. Ora voglio scambiare i valori sotto questa colonna per due righe. Come è stato possibile farlo? Due hack che conosco sono:

  1. Elimina entrambe le righe e inseriscile di nuovo.
  2. Aggiorna le righe con un altro valore e scambia, quindi aggiorna al valore effettivo.

Ma non voglio andare per questi in quanto non sembrano essere la soluzione appropriata al problema. Qualcuno può aiutarmi?

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

Tutte le risposte

Translate

Penso che dovresti optare per la soluzione 2. Non esiste una funzione di "scambio" in nessuna variante SQL che conosco.

Se è necessario farlo regolarmente, suggerisco la soluzione 1, a seconda di come altre parti del software utilizzano questi dati. Puoi avere problemi di blocco se non stai attento.

Ma in breve: non c'è altra soluzione oltre a quelle che hai fornito.

fonte
Translate

La parola magica èDIFFERIBILEQui:

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;

RISULTATO:

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

In seguito alla risposta di Andy Irving

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

chiave: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2

e devo scambiare LNUM in modo che il risultato sia

chiave: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0

l'SQL necessario:

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

Esiste un altro approccio che funziona con SQL Server: utilizzare un join di tabella temporanea nell'istruzione UPDATE.

Il problema è causato dalla presenza di due righe con lo stesso valoreallo stesso tempo, ma se aggiorni entrambe le righe contemporaneamente (ai loro nuovi valori univoci), non si verifica alcuna violazione dei vincoli.

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

fonte
Translate

Penso anche che # 2 sia la scommessa migliore, anche se sarei sicuro di includerlo in una transazione nel caso in cui qualcosa vada storto durante l'aggiornamento.

Un'alternativa (da quando hai chiesto) all'aggiornamento dei valori dell'indice univoco con valori diversi sarebbe aggiornare tutti gli altri valori nelle righe a quelli dell'altra riga. In questo modo puoi lasciare da soli i valori dell'indice univoco e, alla fine, ti ritroverai con i dati che desideri. Fare attenzione però, nel caso in cui qualche altra tabella faccia riferimento a questa tabella in una relazione di chiave esterna, che tutte le relazioni nel database rimangano intatte.

fonte
Translate

Supponendo che tu conosca il PK delle due righe che desideri aggiornare ... Funziona in SQL Server, non posso parlare per altri prodotti. SQL è (dovrebbe essere) atomico 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 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;

quindi andrai da:

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

per:

cola    colb
------------
1       a
2       b
fonte
Translate

Ho lo stesso problema. Ecco il mio approccio proposto in PostgreSQL. Nel mio caso, il mio indice univoco è un valore di sequenza, che definisce un ordine utente esplicito sulle mie righe. L'utente mescolerà le righe in un'app Web, quindi invierà le modifiche.

Ho intenzione di aggiungere un trigger "prima". In quel trigger, ogni volta che il mio valore di indice univoco viene aggiornato, cercherò di vedere se qualsiasi altra riga contiene già il mio nuovo valore. Se è così, darò loro il mio vecchio valore e ruberò loro effettivamente il valore.

Spero che PostgreSQL mi permetta di fare questo shuffle nel trigger before.

Ti posterò indietro e ti farò sapere il mio chilometraggio.

fonte
Translate

Per Oracle c'è un'opzione, DEFERRED, ma devi aggiungerla al tuo vincolo.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Per rinviare TUTTI i vincoli rinviabili durante l'intera sessione, è possibile utilizzare l'istruzione ALTER SESSION SET vincoli = DEFERRED.

fonte

fonte
Lis
Translate

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

Quindi puoi copiare il valore A in una variabile, aggiornarlo con il valore B e quindi impostare il valore B dalla tua variabile. Due domande, ma non conosco una soluzione migliore.

fonte
Translate

Oracle ha differito il controllo dell'integrità che risolve esattamente questo problema, ma non è disponibile né in SQL Server né in MySQL.

fonte
Translate

In SQL Server, l'istruzione MERGE può aggiornare le righe che normalmente interrompono un UNIQUE KEY / INDEX. (Ho appena provato questo perché ero curioso.)

Tuttavia, dovresti utilizzare una tabella / variabile temporanea per fornire MERGE con le righe necessarie.

fonte
Translate

1) cambia gli ID per nome

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Per l'input di esempio, l'output è:

studente id

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

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

fonte