Just for testing purpose, so I can apply it to my project.
I have a table, Table1 with IDTable1.
The column is varchar(3) and primary key, not Autonumeric.(Cannot duplicate)
(I have to deal with varchar, can't change it to integer, the company is already using it long time ago.)
The point is that I have straight random numbers with one "gap or space" between a number and another, in this case number "11".
IDTable1
10
12
15
19
13
16
14
18
17
20
21
I need to re-order these numbers as a result like this:
IDTable1
10
11
12
13
14
15
16
17
18
19
20
How can I do that. I know this query doesn't work at all, but I hope you get the idea.
I read about creating a temporary table but don't know if its necessary.
UPDATE Table1
SET IDTable1 = IDTable1 - 1
WHERE IDTable1 > 9
Note: the IDTable1 less than "9" its not necessary.
Because I'm using 01,02,03,04,05,06,07,08,09.
I don't want to complicate this problem, so > "10" is ok.
I hope I explained enough. Thanks!
It's a bad idea to change primary key (and any other values that is referenced to from other tables).
Ok. Then, without primary key. How should it be resolved?
I have not testet this:
update t1
set t1.IDTable1=cast(t2.new_IDTable1 as varchar(3))
from Table1 as t1
inner join (select IDTable1
,row_number() over(order by IDTable1) as new_IDTable1
from Table1
) as t2
on t2.IDTable1=t1.IDTable1
where cast(t2.IDTable1 as int)<>new_IDTable1
I did this using an INT data type. I'll leave it to you to do the conversions to and from VARCHAR. This should work with multiple gaps of varying sizes. Performance on a large table would be dismal but for smaller tables it should be okay.[code]declare @IDTable1 table (
id int
);
insert into @IDTable1 (id)
values
(10),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20),
(21)
select 'Before', *
from @IDTable1;
;with RowNumberAdded
as (
select [id], row_number() over (order by [id]) rn
from @IDTable1
),
JoinedToNext
as (
select
t2.id, t2.rn, t2.id - t1.id - 1 Adjustment
from
RowNumberAdded t1
inner join
RowNumberAdded t2
on t1.rn + 1 = t2.rn
),
RunningTotals
as (
select j.id, j.rn, j.Adjustment
from JoinedToNext j
where j.rn = 2
union all
select
t.id, t.rn, r.Adjustment + t.id - r.id - 1
from
RowNumberAdded t
inner join
RunningTotals r
on r.rn + 1 = t.rn
)
--select * from RunningTotals
update t
set id = t.id - rt.Adjustment
from
@IDTable1 t
inner join
Runningtotals rt
on t.id = rt.id;
select 'After', *
from @IDTable1;[/code]HTH
1 Like
Why? OP may have cascaded update in place,
It's totally awesome!! but my case is about a primary key. Is it possible to apply this?
What results did you get when you attempted it?
I just tried and it looks like with this sample only works with primary key, without it primary key it doesn't.
Ok. But I got a question. This WITH common table expression is something new for me, I'm pretty sure you did it for testing purpose but now I need to apply it to a Trigger in and After Update.
The main idea of the Trigger is to use deleted and inserted table.
What actually works to me for that Trigger is that it contains a DELETE statement then an INSERT statement and what follows is the UPDATE statement that you just helped me with.
The case is about first, deleting a random row with an ID and other columns, then I move it to another random row so I insert it into another ID and next I need to update it so I can reorder those ID's again as it was 
and well as I said, actually I use Varchar and the ID is a composite primary Key and I will reorder a small group of numbers but that's not the problem. I just wanted to reorder those ID's. I appreciate your help!!
But as I asked I only need to know about that WITH CTE, where should it go? after my INSERT statement??
@SwePeso OP might or might not. Results of "missing" a referenced id, can be next to impossible to rectify.
OK. Thanks anyway thumbs up! 
UPDATE Table1
SET IDTable1 = RIGHT('0' + CAST(IDTable1_Starting_Value + IDTable1_Row_Number - 1 AS varchar(3)), 2)
FROM Table1 t1
CROSS JOIN (
SELECT MIN(IDTable1) AS IDTable1_Starting_Value
FROM Table1
) AS cj
INNER JOIN (
SELECT IDTable1, ROW_NUMBER() OVER(ORDER BY IDTable1) AS IDTable1_Row_Number
FROM Table1
) AS ij ON
ij.IDTable1 = t1.IDTable1
Concerning CTEs: The CTE, starting with the ";WITH" portion, acts as a sort of prefix to the actual statement. In this case it's an UPDATE statement but it could be another type. Since it's like a prefix, it MUST be positioned immediately before the code it operates on. It is, in fact, a part of the UPDATE statement. CTE's can be useful. They seem a bit daunting at first glance but are worth learning about. BTW, there's no issue using a CTE inside your trigger.