Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Апдейт записей в таблице с уникальным индексом  [new]
Sergey TSA
Member

Откуда:
Сообщений: 6
Здравствуйте!
Возникла необходимость проапдейтить 2 записи в таблице, на которой есть уникальный индекс.
Причем при апдейте первой записи возникает нарушение этой уникальности и апдейт не проходит.
Это нарушение не возникало бы, если бы можно было проапдейтить и 1ю и 2ю запись, но до 2го апдейта дело не доходит.

Пример таблицы:

create table TBL1 
	(PK int identity primary key,
	FLD1 int not null,
	FLD2 int not null,
	FLD3 int)
go
create unique index IDX1 on TBL1 (FLD1, FLD2)
go

insert into TBL1 (FLD1, FLD2, FLD3) values (1, 1, 1)
insert into TBL1 (FLD1, FLD2, FLD3) values (2, 1, 2)


В реальной таблице FK1 - это референс на другую таблицу
Хочется выполнить такие запросы:

update TBL1 
	set FLD1 = 2
	where PK = 1

update TBL1 
	set FLD1 = 1
	where PK = 2


Естественно, 1й апдейт отваливается. Есть какие-нибудь рекомендации, как этого лучше всего добиться?
В голову приходят 2 варианта:
1. Вообще убрать уникальный индекс. Но тогда теряется контроль уникальности на уровне БД.
2. Сначала удалить обе записи, потом заинсертить. Тоже вариант не очень нравится - записей может быть и больше двух.
Есть еще какие-нибудь варианты?
26 сен 18, 14:35    [21686800]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
court
Member

Откуда:
Сообщений: 2021
update TBL1 
	set FLD1 = case when PK=1 then 2 when PK=2 then 1 end
	where PK in (1,2)

?
26 сен 18, 14:42    [21686807]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
Sergey TSA
Member

Откуда:
Сообщений: 6
court,

Спасибо, это конечно сработает, но хотелось бы тогда усложнить вопрос) На самом деле мы апдейтим по одной записи, через хранимку:

create procedure UpdateTbl1
	@Fld1 int, 
	@PK int
as begin
	update TBL1
		set FLD1 = @Fld1
		where PK = @PK
end


Есть ощущение, что с таким подходом искомого результата не добиться, но вдруг все же есть решение
26 сен 18, 15:30    [21686894]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Sergey TSA,

Проставить на одном любое значение (99999) потом менять оба.
в транзакции отключить констрейн менять включить констрейн
26 сен 18, 15:37    [21686908]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Включение констрейнта очень накладно, на больших таблицах однозначно не стоит так делать.

Вообще оптимальный вариант это просто создать новую процедуру которая вам сделает нужный апдейт, других приемлемых вариантов нет...
26 сен 18, 15:54    [21686933]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
TaPaK
Sergey TSA,

Проставить на одном любое значение (99999) потом менять оба.
в транзакции отключить констрейн менять включить констрейн
Не 99999, а NULL. Тогда и констрейнт отключать не придётся.
Естественно, поля FLD1 и FLD2 должны допускать значение NULL.
26 сен 18, 16:05    [21686951]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
iap
TaPaK
Sergey TSA,

Проставить на одном любое значение (99999) потом менять оба.
в транзакции отключить констрейн менять включить констрейн
Не 99999, а NULL. Тогда и констрейнт отключать не придётся.
Естественно, поля FLD1 и FLD2 должны допускать значение NULL.

ну такого(nullable) по условиям нет
26 сен 18, 16:17    [21686970]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Sergey TSA
Есть ощущение, что с таким подходом искомого результата не добиться, но вдруг все же есть решение
Например, перепишите так:
create procedure BatchUpdateTBL1
 @data xml
as
begin
 update t
  set
   FLD1 = s.FLD1
 from
  @d.nodes('/item') d(n) cross apply
  (select d.n.value('@PK', 'int'), d.n.value('@FLD1', 'int')) s(PK, FLD1) join
  TBL1 t on t.PK = s.PK
end;
go

create procedure UpdateTbl1
	@Fld1 int, 
	@PK int
as begin
 declare @data xml

 select @data = t.x from (select @PK as [@PK], @Fld1 as [@FLD1] for xml path('item'), type) t(x);

 exec BatchUpdateTBL1 @data;
end;
go

Тогда, не меняяя логику работы с UpdateTbl1, сможете сделать так:
...
declare @data xml;

select
 @data = t.x
from
 (select PK as [@PK], Fld1 as [@FLD1] from (values (1, 2), (2, 1)) a(PK, Fld1) for xml path('item'), type) t(x);

exec BatchUpdateTBL1 @data;
...

Вместо xml можно применить табличный тип, при условии, что его определение не придется менять.
26 сен 18, 16:23    [21686986]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
TaPaK
Проставить на одном любое значение (99999) потом менять оба.
Но наверняка нужно, что бы много пользователей могло выполнить эту процедуру.
Поэтому нужно сделать механизм хранения свободных резервных FLD для обмена. А логику реализовать в instead-off триггере :-)
Что делать, придётся извращаться, раз накладываются всякие ограничения, типа "На самом деле мы апдейтим по одной записи"
26 сен 18, 16:23    [21686987]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
Sergey TSA
Member

Откуда:
Сообщений: 6
Всем спасибо!

TaPaK
iap
пропущено...
Не 99999, а NULL. Тогда и констрейнт отключать не придётся.
Естественно, поля FLD1 и FLD2 должны допускать значение NULL.

ну такого(nullable) по условиям нет


Ну можно и нулл, но все равно уникальность нарушится, если кто-то еще будет менять другие записи.
автор
Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.


Владимир Затуливетер
Включение констрейнта очень накладно, на больших таблицах однозначно не стоит так делать.

Вообще оптимальный вариант это просто создать новую процедуру которая вам сделает нужный апдейт, других приемлемых вариантов нет...


Вот, я надеялся, что есть и другие простые, приемлемые решения, но видимо придется действительно что-то заковыристое делать, чтобы сразу все записи апдейтились.

invm, спасибо, попробую.
26 сен 18, 17:04    [21687077]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Sergey TSA,

автор
Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.

не смогли перевести?
26 сен 18, 17:05    [21687082]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
aleks222
Member

Откуда:
Сообщений: 985
Sergey TSA
Ну можно и нулл, но все равно уникальность нарушится, если кто-то еще будет менять другие записи.

Фильтрованные индексы?
Не, не слыхал.
26 сен 18, 19:39    [21687330]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Sergey TSA
Хочется выполнить такие запросы:

update TBL1 
	set FLD1 = 2
	where PK = 1

update TBL1 
	set FLD1 = 1
	where PK = 2



А можно узнать, что за задача у вас. По сути вам нужно поменять местами два id при этом отдельными запросами (что без третьего id невозможно). Но я не могу придумать зачем бы мне такое было нужно на постоянной основе. Или это у вас разовая акция?
26 сен 18, 22:11    [21687455]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
aleks222
Member

Откуда:
Сообщений: 985
PizzaPizza
Но я не могу придумать зачем бы мне такое было нужно

Ээээ, батенька, больное воображение иных "архитекторов" ишо не такие кундштюки может придумывать.
27 сен 18, 05:57    [21687656]     Ответить | Цитировать Сообщить модератору
 Re: Апдейт записей в таблице с уникальным индексом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
aleks222
PizzaPizza
Но я не могу придумать зачем бы мне такое было нужно

Ээээ, батенька, больное воображение иных "архитекторов" ишо не такие кундштюки может придумывать.
Например, уникальное поле сортировки (приоритета)
27 сен 18, 11:01    [21687887]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить