Tim Schmelter Tim Schmelter - 11 months ago 52
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

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

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


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 Source

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

declare @ToDelete int
set @ToDelete = 22

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


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