update - sql elenco tabelle database




Scambia valori di colonna indicizzati univoci nel database (8)

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 si può fare? Due hack che conosco sono:

  1. Elimina entrambe le righe e reinseriscile.
  2. Aggiorna le righe con qualche altro valore e scambia, quindi aggiorna al valore effettivo.

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


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 è:

id studente

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

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


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.

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

e devo scambiare LNUM in modo che il risultato sia

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

Esiste un altro approccio che funziona con SQL Server: utilizzare una tabella temporanea per unirsi ad esso nell'istruzione UPDATE.

Il problema è causato dall'avere due righe con lo stesso valore contemporaneamente , ma se si aggiornano entrambe le righe contemporaneamente (ai loro nuovi valori univoci), non vi è 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


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 nelle mie righe. L'utente mescolerà le righe in un'app Web, quindi invierà le modifiche.

Sto programmando di aggiungere un trigger "prima". In quel trigger, ogni volta che il mio valore di indice univoco viene aggiornato, cercherò di vedere se un'altra riga contiene già il mio nuovo valore. In tal caso, darò loro il mio vecchio valore e li ruberò efficacemente.

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

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


La parola magica è DEFERRABILE qui:

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)

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


Penso che dovresti optare per la soluzione 2. Non esiste alcuna 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 esiste altra soluzione se non quella che hai fornito.


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

SET CONSTRAINT emp_no_fk_par DEFERRED; 

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

Source





database