Aquí podría ser tu PUBLICIDAD


Cambiar valores únicos de columna indexada en la base de datos

votos
47

Tengo una tabla de base de datos y uno de los campos (no la clave principal) tiene un índice único. Ahora quiero intercambiar valores debajo de esta columna por dos filas. ¿Como se puede hacer esto? Dos ataques que conozco son:

  1. Borre ambas filas y vuelva a insertarlas
  2. Actualice las filas con algún otro valor e intercambie y luego actualice al valor real.

Pero no quiero ir por estos ya que no parecen ser la solución adecuada al problema. ¿Podría alguien ayudarme?

Publicado el 03/08/2008 a las 10:55
fuente por usuario Ramesh Soni
En otros idiomas...        العربية       

11 respuestas

votos
21

La palabra mágica es DEFERRABLE aquí:

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)
Respondida el 15/09/2012 a las 01:38
fuente por usuario wildplasser


Aquí podría ser tu PUBLICIDAD


votos
8

Creo que deberías buscar la solución 2. No hay una función de 'intercambio' en ninguna variante SQL que conozca.

Si necesita hacer esto regularmente, sugiero la solución 1, dependiendo de cómo otras partes del software estén usando esta información. Puede tener problemas de bloqueo si no tiene cuidado.

Pero en resumen: no hay otra solución que las que brindó.

Respondida el 03/08/2008 a las 01:26
fuente por usuario Daan

votos
5

Hay otro método que funciona con SQL Server: utilizar una tabla temporal unirse a ella en su instrucción UPDATE.

El problema es causado por tener dos filas con el mismo valor , al mismo tiempo , pero si se actualiza dos filas a la vez (a sus nuevos valores únicos), no hay ninguna violación de la restricción.

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)

Gracias a Rich H para esta técnica. - Marca

Respondida el 04/04/2012 a las 08:40
fuente por usuario Mark Gaulin

votos
5

Además de la respuesta de Andy Irving

esto funcionó para mí (en SQL Server 2005) en una situación similar donde tengo una clave compuesta y necesito intercambiar un campo que es parte de la restricción única.

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

y necesito cambiar LNUM para que el resultado sea

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

el SQL necesario:

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))
Respondida el 10/12/2008 a las 01:19
fuente por usuario MakisCE

votos
2

Tengo el mismo problema. Aquí está mi enfoque propuesto en PostgreSQL. En mi caso, mi índice único es un valor de secuencia, que define un orden de usuario explícito en mis filas. El usuario barajará filas en una aplicación web y luego enviará los cambios.

Estoy planeando agregar un disparador "antes". En ese disparador, cada vez que se actualice mi valor de índice único, veré si hay alguna otra fila que ya tenga mi nuevo valor. Si es así, les daré mi valor anterior y les quitaré el valor de manera efectiva.

Espero que PostgreSQL me permita hacer esta mezcla en el disparador anterior.

Volveré a publicar y le dejaré saber mi kilometraje.

Respondida el 24/06/2009 a las 04:58
fuente por usuario the.jxc

votos
2

Suponiendo que conozca el PK de las dos filas que desea actualizar ... Esto funciona en SQL Server, no puede hablar de otros productos. SQL es (se supone que es) atómico en el nivel de declaración:

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;

entonces irás de:

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

a:

cola    colb
------------
1       a
2       b
Respondida el 16/09/2008 a las 03:57
fuente por usuario Andy Irving

votos
2

También creo que el # 2 es la mejor opción, aunque me aseguraré de envolverlo en una transacción en caso de que algo salga mal a mediados de la actualización.

Una alternativa (desde que lo solicitó) para actualizar los valores de Índice único con valores diferentes sería actualizar todos los demás valores en las filas a la de la otra fila. Hacer esto significa que puede dejar los valores del Índice único solo, y al final, termina con los datos que desea. Sin embargo, tenga cuidado, en caso de que alguna otra tabla haga referencia a esta tabla en una relación de clave externa, que todas las relaciones en la base de datos permanezcan intactas.

Respondida el 03/08/2008 a las 02:22
fuente por usuario Yaakov Ellis

votos
1

Para Oracle no es una opción, diferido, pero hay que añadirlo a su restricción.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Aplazar todas las restricciones que son diferible durante toda la sesión, se puede utilizar el ALTER SESSION SET limitaciones = instrucción diferida.

Fuente

Respondida el 27/04/2016 a las 11:15
fuente por usuario TheBakker

votos
1

En SQL Server, la instrucción MERGE puede actualizar filas que normalmente romper una clave única / INDEX. (Just probado esto porque tenía curiosidad.)

Sin embargo, habría que utilizar una tabla temp / variable para suministrar MERGE w / las filas necesarias.

Respondida el 20/04/2012 a las 08:12
fuente por usuario Ben Mosher

votos
1

Oracle ha aplazado la comprobación de la integridad que resuelve exactamente esto, pero no está disponible en SQL Server o MySQL.

Respondida el 19/03/2010 a las 08:29
fuente por usuario BlueRaja - Danny Pflughoeft

votos
0

Pienso generalmente de un valor que absolutamente ningún índice en mi mesa podría tener. Por lo general, - para los valores de columna única - que es muy fácil. Por ejemplo, para valores de la columna 'posición' (información sobre el orden de varios elementos) es 0.

A continuación, puede copiar el valor de A a una variable, que se actualizará con el valor de B y luego establecer el valor B de la variable. Dos consultas, sé hay una solución mejor sin embargo.

Respondida el 18/03/2017 a las 03:00
fuente por usuario Lis


Aquí podría ser tu PUBLICIDAD