Tim Schmelter Tim Schmelter - 1 month ago 6
SQL Question

How to combine an update and delete in a single sql statement without vialoating index constraints?

Is it somehow possible to execute following sql statement without violating a unique index constraint that ensures that the

Position
is unique?

UPDATE wl SET Position = Position - 1
FROM [dbo].[WatchList] wl
WHERE Position > ( SELECT Position FROM [dbo].[WatchList] wl2
WHERE WatchListId = @WatchListID );
DELETE FROM [dbo].[WatchList] WHERE WatchListID = @WatchListID


I want to ensure that no Positon-gaps occur when i delete one record. All records with a higher position should be updated with
Position=Position-1
.

But that will cause a unique index violation because the row is yet not deleted. Are the only ways to prevent this issue to ...


  • use a stored-procedure (should be avoided if possible, logic should be in code)

  • to determine the old position before i delete the record which requires two queries



Update:

Thanks for your efforts. However, since there is no easy solution for this i have used the second approach, so first determine the old postion, delete the record and then update the followers.

Answer

If you want to run two different (types of) statements, and have constraints maintained both before and after, use MERGE:

create table T (
    ID int not null,
    Position int not null,
    constraint PK_T_ID PRIMARY KEY (ID),
    constraint UQ_T_ID UNIQUE (Position))

insert into T(ID,Position) values
(12,1),
(22,2),
(36,3),
(47,4)


declare @ToDelete int
set @ToDelete = 22

;With Positions as (
    select
        Position
    from
        T
    where
        Position >= (select Position from T where ID = @ToDelete)
)
merge into T t
using (select Position from Positions) s
on
    t.Position = s.Position
when matched and ID = @ToDelete
then delete
when matched then update set Position = t.Position -1
;
select * from T

Results:

ID          Position
----------- -----------
12          1
36          2
47          3
Comments