How to use SQL variables in an Update Statements Where Variable is also Updated for each row during the Update Process

Recently I had to update the SortNumber column of a table for rows which have the NULL value as sort number values in SortNumber column.

And those update values for the table should begin from the following value of the maximum sort number column value and increase with interval 1 for each row.

I used the below statement which I've used before for similar reasons on other sql tables. And I thought you may also need a such sql update statements for your sql codes in your applications.

DECLARE @i SMALLINT
SET @i = 70

UPDATE RequestType
SET
    @i = @i + 1,
    OrderNumber = @i
WHERE OrderNumber IS NULL

 

In the above sql statement the trick is the update line for the sql variable @i, @i = @i + 1

Here @i is also changing for each row in the update statement. And the following line, OrderNumber = @i uses the value of the updated or changed sql variable @i value.

If you look at the sample you can see how you can benefit from using changing variables in update method to create unique names or codes for each row or record in the table.

Create Table Controls (
    id uniqueidentifier not null,
    typeid int not null,
    code nvarchar(10) null,
    Sort int null,
)

GO

insert into Controls select newid(), 1, null, null
insert into Controls select newid(), 1, null, 1
insert into Controls select newid(), 1, null, null
insert into Controls select newid(), 2, null, null
insert into Controls select newid(), 2, null, 3
insert into Controls select newid(), 3, null, null
insert into Controls select newid(), 1, null, null
insert into Controls select newid(), 3, null, 7
insert into Controls select newid(), 2, null, null

select * from Controls

DECLARE @i SMALLINT
SET @i = 0
UPDATE Controls
SET
    @i = @i + 1,
    Sort = ISNULL(Sort, @i),
    Code = 'Control-' + LTRIM(STR(@i))

select * from Controls
 

In the end the rows for the returned record set of the select statement is just like in the below image.

In short, as you see in the above sample sql codes, you can also update a sql variable that you have declared earlier whose value changes with each row during the process of a t-sql update code statement. And use that sql variable in the same sql update code.

I began to use this tip & trick in my sql codes frequently these days, so you can use and see how you can benefit these codes in your sql developments.

Enjoy your programming.