tipos - modificar columna sql

Intercambie valores de columna indexados únicos en la base de datos

Question

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

1. Elimine 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?

Answer 1

1) cambie los identificadores por nombre

id student
1 Abbot
2 Doris
3 Emerson
4 Green
5 Jeames 

Para la entrada de muestra, la salida es:

estudiante de id

1 Doris
2 Abbot
3 Green
4 Emerson
5 Jeames 

"en caso de que n número de filas cómo se gestionará ......"

Answer 2

Además de la respuesta de Andy Irving

esto funcionó para mí (en SQL Server 2005) en una situación similar en la que 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))

Answer 3

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

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

Pero en resumen: no hay otra solución que la que proporcionó.

Answer 4

En SQL Server, la instrucción MERGE puede actualizar filas que normalmente romperían una CLAVE / ÍNDICE ÚNICO. (Solo probé esto porque tenía curiosidad).

Sin embargo, tendría que usar una tabla / variable temporal para suministrar MERGE con las filas necesarias.

Answer 5

Hay otro enfoque que funciona con SQL Server: use una tabla temporal para unirse a él en su instrucción UPDATE.

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

Pseudocó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 por esta técnica. - Marca

Answer 6

La palabra mágica es DEFERRABLE aquí:

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;

RESULTADO:

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)

Answer 7

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

Answer 8

Para Oracle hay una opción, DEFERRED, pero debe agregarla a su restricción.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Para diferir TODAS las restricciones que son diferibles durante toda la sesión, puede usar la instrucción ALTER SESSION SET restricciones = DEFERRED.

Source

Answer 9

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

Luego puede copiar el valor A a una variable, actualizarlo con el valor B y luego establecer el valor B desde su variable. Dos consultas, sin embargo, no conozco una solución mejor.

Answer 10

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

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;

entonces irás de:

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

a:

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

Answer 11

También creo que el # 2 es la mejor apuesta, aunque me aseguraría de incluirlo en una transacción en caso de que algo salga mal a mitad de la actualización.

Una alternativa (ya que solicitó) para actualizar los valores del Índice Único con valores diferentes sería actualizar todos los demás valores en las filas a los de la otra fila. Hacer esto significa que puede dejar solo los valores del Índice único y, al final, terminar 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 permanecen intactas.

Answer 12

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 explícito de usuario 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, buscaré si alguna otra fila ya contiene mi nuevo valor. Si es así, les daré mi valor anterior y les robaré efectivamente el valor.

Espero que PostgreSQL me permita hacer este shuffle en el activador anterior.

Volveré a publicar y les haré saber mi kilometraje.