Trocar valores coluna indexada únicas no banco de dados

votos
47

Eu tenho uma tabela de banco de dados e um dos campos (não a chave primária) está tendo um índice exclusivo sobre ele. Agora eu quero trocar valores nesta coluna para duas linhas. Como isso poderia ser feito? Dois hacks que eu conheço são:

  1. Excluir ambas as filas e re-inseri-los
  2. Atualizar linhas com algum outro valor e trocar e atualizar a valor real.

Mas eu não quero ir para estes como eles não parecem ser a solução adequada para o problema. Alguém poderia me ajudar?

Publicado 03/08/2008 em 10:55
fonte usuário
Em outras línguas...                            


12 respostas

votos
21

A palavra mágica é DEFERRABLE aqui:

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;

RESULTADO:

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)
Respondeu 15/09/2012 em 13:38
fonte usuário

votos
8

Acho que você deveria ir para a solução 2. Não há nenhuma função 'swap' em qualquer variante SQL eu saiba.

Se você precisa fazer isso regularmente, sugiro solução 1, dependendo de como outras partes do software estão usando esses dados. Você pode ter problemas de bloqueio se você não tiver cuidado.

Mas em suma: não há outra solução do que os que você forneceu.

Respondeu 03/08/2008 em 13:26
fonte usuário

votos
5

Há uma outra abordagem que funciona com o SQL Server: usar uma tabela temporária se juntar a ele em sua declaração UPDATE.

O problema é causado por ter duas linhas com o mesmo valor , ao mesmo tempo , mas se você atualizar ambas as linhas de uma vez (para os valores novos, originais), não há violação de restrição.

Pseudo-código:

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

Graças ao rico H para esta técnica. - Mark

Respondeu 04/04/2012 em 20:40
fonte usuário

votos
5

Na sequência da resposta de Andy Irving

Isso funcionou para mim (em SQL Server 2005) em uma situação semelhante, onde tenho uma chave composta e eu preciso trocar um campo que faz parte da restrição exclusiva.

chave: PID, rec1 lnum: 10, 0 rec2: 10, 1 rec3: 10, 2

e eu preciso trocar lnum para que o resultado é

chave: PID, rec1 lnum: 10, 1 rec2: 10, 2 rec3: 10, 0

o SQL necessário:

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))
Respondeu 10/12/2008 em 13:19
fonte usuário

votos
2

Eu tenho o mesmo problema. Aqui está a minha abordagem proposta no PostgreSQL. No meu caso, meu índice exclusivo é um valor seqüência, a definição de um user-ordem explícita nas minhas linhas. O usuário irá embaralhar linhas em torno de um web-app, em seguida, apresentar as alterações.

Estou planejando adicionar um "antes" gatilho. Nesse gatilho, sempre que o meu valor índice exclusivo é atualizado, vou olhar para ver se qualquer outra linha já detém o meu novo valor. Se assim for, vou dar-lhes o meu valor antigo, e eficazmente roubar o valor fora deles.

Eu estou esperando que PostgreSQL vai me permitir fazer isso shuffle na gatilho antes.

Vou postar de volta e que você saiba a minha quilometragem.

Respondeu 24/06/2009 em 04:58
fonte usuário

votos
2

Supondo que você sabe o PK das duas linhas que deseja atualizar ... Isso funciona no SQL Server, não posso falar por outros produtos. SQL é (supostamente) atômica no nível de instrução:

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;

assim você vai de:

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

para:

cola    colb
------------
1       a
2       b
Respondeu 16/09/2008 em 15:57
fonte usuário

votos
2

Eu também acho que # 2 é a melhor aposta, embora eu estaria certo para envolvê-lo em uma transação no caso de algo der errado no meio da atualização.

Uma alternativa (desde que você pediu) para atualizar os valores índice exclusivo com valores diferentes seria para atualizar todos os outros valores nas linhas para o da outra linha. Fazer isso significa que você pode deixar os valores índice exclusivo sozinho, e no final, você acaba com os dados que você quer. Tenha cuidado, porém, no caso de algumas outras referências da tabela esta tabela em um relacionamento de chave estrangeira, que todos os relacionamentos no DB permanecem intactos.

Respondeu 03/08/2008 em 14:22
fonte usuário

votos
1

1) mudar os ids para nome

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Para a amostra de entrada, a saída é:

estudante id

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

"No caso de n número de linhas como irá gerir ......"

Respondeu 06/11/2018 em 08:56
fonte usuário

votos
1

Para o Oracle não é uma opção, adiado, mas você tem que adicioná-lo à sua restrição.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Para adiar todas as restrições que são deferrable durante toda a sessão, você pode usar o ALTER SESSION SET restrições = declaração diferido.

Fonte

Respondeu 27/04/2016 em 14:15
fonte usuário

votos
1

No SQL Server, a instrução MERGE pode atualizar linhas que normalmente quebrar uma UNIQUE KEY / INDEX. (Apenas testado isso porque eu estava curioso.)

No entanto, você teria que usar um temp table / variável para fornecer MERGE w / as linhas necessárias.

Respondeu 20/04/2012 em 20:12
fonte usuário

votos
1

A Oracle tem adiado verificação de integridade que resolve exatamente isso, mas ele não está disponível em qualquer SQL Server ou MySQL.

Respondeu 19/03/2010 em 20:29
fonte usuário

votos
0

Eu costumo pensar em um valor que absolutamente nenhum índice na minha mesa poderia ter. Geralmente - para os valores de coluna exclusivos - é realmente fácil. Por exemplo, para valores de 'posição' (as informações sobre a ordem de vários elementos) coluna é 0.

Em seguida, você pode copiar o valor Uma a uma variável, atualizá-lo com valor B e, em seguida, definir o valor B de sua variável. Duas consultas, eu sei que não há solução melhor ainda.

Respondeu 18/03/2017 em 18:00
fonte usuário

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