Обмен уникальных индексированных значений столбцов в базе данных

У меня есть таблица базы данных, и одно из полей (не первичный ключ) имеет уникальный индекс. Теперь я хочу поменять местами значения в этом столбце для двух строк. Как это можно сделать? Два хака, которых я знаю:

  1. Удалите обе строки и вставьте их заново.
  2. Обновите строки с другим значением и поменяйте местами, а затем обновите до фактического значения.

Но я не хочу идти на это, потому что они не кажутся подходящим решением проблемы. Кто-нибудь может мне помочь?

3.08.2008 09:55:26
12 ОТВЕТОВ
РЕШЕНИЕ

Я думаю, что вы должны пойти на решение 2. В любом варианте SQL, который я знаю, нет функции «подкачки».

Если вам нужно делать это регулярно, я предлагаю решение 1, в зависимости от того, как другие части программного обеспечения используют эти данные. У вас могут быть проблемы с блокировкой, если вы не будете осторожны.

Но вкратце: нет другого решения, кроме того, которое вы предоставили.

15
3.08.2008 12:26:35

Волшебное слово НЕЗАВИСИМО здесь:

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;

РЕЗУЛЬТАТ:

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)
29
15.09.2012 12:38:09
@MarcoDemaio Я не знаю. Боюсь, что нет: поскольку mysql не разрешает обновления от самостоятельных объединений, я полагаю, что он делает грязное чтение. Но вы можете попробовать ...
wildplasser 27.12.2012 18:41:06
Это не поддерживается в MS SQL Server.
PhillipM 25.03.2019 13:15:37
Может быть, Microsoft не поддерживает это. (sql-server не является одним из тегов) Поскольку вы заплатили за него, вы, возможно, могли бы попросить об этом?
wildplasser 25.03.2019 23:00:10

В дополнение к ответу Энди Ирвинга

это сработало для меня (на SQL Server 2005) в аналогичной ситуации, когда у меня есть составной ключ, и мне нужно поменять поле, которое является частью уникального ограничения.

ключ: PID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2

и мне нужно поменять LNUM так, чтобы результат

ключ: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0

SQL необходим:

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))
6
10.12.2008 12:19:55
Если это работает, это здорово, потому что это можно сделать за одну транзакцию
codeulike 23.09.2009 20:06:06

Есть другой подход, который работает с SQL Server: используйте временное присоединение к таблице в своем выражении UPDATE.

Проблема вызвана тем, что две строки имеют одно и то же значение одновременно , но если вы обновите обе строки одновременно (их новые уникальные значения), нарушения ограничений не будет.

Псевдо-код:

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

Спасибо Rich H за эту технику. - Отметка

5
4.04.2012 19:40:12
Возможно, это немного устарело, но я пытался сделать страницу «переупорядочения» для своего приложения silverlight, так как клиент хотел отсортировать свои отчеты по определенному порядку - я добавил столбец сортировки, но так как это уникальный ключ, я был возникли проблемы с его обновлением. В итоге я использовал переменную таблицы, но принцип тот же (честно говоря, я не очень люблю временные таблицы). Спасибо за идею :)
Charleh 29.06.2012 00:18:11

Я также считаю, что № 2 - лучшая ставка, хотя я бы обязательно включил ее в транзакцию на случай, если что-то пойдет не так во время обновления.

Альтернативой (так как вы просили) обновить значения уникального индекса другими значениями было бы обновить все другие значения в строках до значения другой строки. Это означает, что вы можете оставить значения уникального индекса в покое, и в итоге вы получите нужные данные. Однако будьте осторожны, если какая-то другая таблица ссылается на эту таблицу в отношении внешнего ключа, все отношения в БД остаются неизменными.

3
3.08.2008 13:22:25

Предполагая, что вы знаете PK двух строк, которые вы хотите обновить ... Это работает в SQL Server, не может говорить о других продуктах. SQL является (должен быть) атомарным на уровне операторов:

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;

так вы пойдете из:

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

чтобы:

cola    colb
------------
1       a
2       b
3
18.04.2016 07:11:25

У меня такая же проблема. Вот мой предложенный подход в PostgreSQL. В моем случае мой уникальный индекс - это значение последовательности, определяющее явный порядок пользователя в моих строках. Пользователь будет перемешивать строки в веб-приложении, а затем отправлять изменения.

Я планирую добавить триггер «до». В этом триггере всякий раз, когда обновляется мое уникальное значение индекса, я проверяю, содержит ли какая-либо другая строка мое новое значение. Если так, я передам им свою старую ценность и эффективно украду их ценность.

Я надеюсь, что PostgreSQL позволит мне сделать это в случайном порядке перед триггером.

Я отправлю назад и сообщу мой пробег.

2
24.06.2009 03:58:34

Для Oracle есть опция DEFERRED, но вы должны добавить ее к своему ограничению.

SET CONSTRAINT emp_no_fk_par DEFERRED; 

Чтобы отложить ВСЕ ограничения, которые откладываются в течение всего сеанса, вы можете использовать инструкцию ALTER SESSION SET constraints = DEFERRED.

Источник

2
27.04.2016 14:15:25

Я обычно думаю о значении, которое абсолютно никакой индекс в моей таблице не может иметь. Обычно - для уникальных значений столбцов - это действительно легко. Например, для значений столбца position (информация о порядке нескольких элементов) это 0.

Затем вы можете скопировать значение A в переменную, обновить его значением B, а затем установить значение B из вашей переменной. Два вопроса, я не знаю лучшего решения, хотя.

2
18.03.2017 18:00:46

Oracle имеет отложенную проверку целостности, которая решает именно это, но она недоступна ни в SQL Server, ни в MySQL.

1
19.03.2010 19:29:59

В SQL Server оператор MERGE может обновлять строки, которые обычно нарушают UNIQUE KEY / INDEX. (Просто проверил это, потому что мне было любопытно.)

Однако вам придется использовать временную таблицу / переменную для предоставления MERGE с необходимыми строками.

1
20.04.2012 19:12:34
У MERGE есть другие проблемы ... и то же самое можно сделать с помощью цикла DELETE / UPDATE / INSERT; если UPDATE выполняется как один оператор (см. UPDATE..JOIN), он не будет нарушать какие-либо ограничения PK / AK / UX, поскольку ограничения применяются только к конечному результату оператора. Так можно ли использовать MERGE вместо UPDATE..JOIN для решения этой проблемы? Конечно .. потому что это о том, что это внутренне.
user2864740 23.11.2019 02:02:10

1) переключить идентификаторы для имени

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

Для примера ввода вывод:

студент

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

"в случае n количество строк, как обойдется ......"

1
6.11.2018 18:31:42