Update data in a cursor row not yet fetched sql server

Question

I need update the "Order" column of a table before I add a unique constraint to avoid error on adding the constraint. To update this field I'm trying do the following T-SQL code:

DECLARE c_x CURSOR FOR
SELECT ID, ISOLD, ISNEW, GROUPID, ORDER, ISENABLED
FROM mytable

OPEN c_x;
FETCH NEXT FROM c_x INTO @ID, @ISOLD, @ISNEW, @GROUPID, @ORDER, @ISENABLED;

WHILE @@FETCH_STATUS = 0  
BEGIN  
SELECT @SUMOFROWS = COUNT(*) FROM mytable
    WHERE ISOLD = @ISOLD
        AND ISNEW = @ISNEW
        AND GROUPID = @GROUPID
        AND ORDER = @ORDER
        AND ISENABLED = @ISENABLED;


IF @SUMOFROWS > 1
        BEGIN
            UPDATE mytable
            SET ORDER = ORDER+ 1
            WHERE ISOLD =  @ISOLD 
            AND ISNEW =  @ISNEW 
            AND GROUPID =  @GROUPID 
            AND ISENABLED =  @ISENABLED 
            AND ORDER >= @ORDER 
            AND ID <> @ID

        END

    FETCH NEXT FROM CURSOR_TIPOS_AGENDA INTO @ID, @ISOLD, @ISNEW, @GROUPID, @ORDER, @ISENABLED
END 

Table Data:

ID   ISOLD  ISNEW  GROUPID ORDER ISENABLED
1    1      0      500     1      1
2    0      0      500     1      1
3    0      0      500     1      1

THE NEW UNIQUE CONSTRAINT

CREATE UNIQUE NONCLUSTERED INDEX [XAK1_mytable] ON mytable([ORDER], [ISENABLED], [ISOLD`], [ISNEW], [GROUPID])

THE ERROR ON ADD THE UNIQUE CONSTRAINT OCCURS IN TABLE ROW WITH ID 3. THE CODE ABOVE SHOULD UPDATE THE ROW WITH ID 3 WITH THE ORDER 2

But this code does not update the rows of the table as well. Someone know how can I update the rows I not fetched yet in the cursor?

Answer 1

It looks like you are trying to make Order unique for each set of GroupId, [Order], IsOld, IsNew, IsEnabled.

You can do this with a set based statement using a common table expression with row_number()

;with cte as (
  select 
      Id
    , IsOld
    , IsNew
    , GroupId
    , [Order]
    , IsEnabled
    , rn = row_number() over (
        partition by GroupId, [Order], IsOld, IsNew, IsEnabled
        order by [Order]
        ) - 1
  from t
  )
--Preview: 
--/*
select *, NewOrder = [Order] + rn
from cte
where rn > 0
--*/
/*
--Update
update cte 
  set [Order] = [Order] + rn
where rn > 0;
--*/

---

You can skip using the cte like so:

update cte
  set [Order] = [Order] + rn
from (
  select *
    , rn = row_number() over (
        partition by GroupId, [Order], IsOld, IsNew, IsEnabled
        order by [Order]
        ) - 1
  from t
  ) as cte
where rn > 0

---

For the given example above, the select code would return:

+----+-------+-------+---------+-------+-----------+----+----------+
| Id | IsOld | IsNew | GroupId | Order | IsEnabled | rn | NewOrder |
+----+-------+-------+---------+-------+-----------+----+----------+
|  3 |     0 |     0 |     500 |     1 |         1 |  1 |        2 |
+----+-------+-------+---------+-------+-----------+----+----------+

and after running the update, the table would look like this:

+----+-------+-------+---------+-------+-----------+
| Id | IsOld | IsNew | GroupId | Order | IsEnabled |
+----+-------+-------+---------+-------+-----------+
|  1 |     1 |     0 |     500 |     1 |         1 |
|  2 |     0 |     0 |     500 |     1 |         1 |
|  3 |     0 |     0 |     500 |     2 |         1 |
+----+-------+-------+---------+-------+-----------+

test setup: http://rextester.com/XJAUJ47591