SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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




Running Sum T-SQL variables in Update Statements


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
Code

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
Code

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

t-sql update using variables

 

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.