Update in SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Update in SQL Server

Author

Topic

sanky5
Starting Member

1 Post

Posted - 2013-04-30 : 05:25:57

I have a requirement in SQL Server where in one of the DB tables i need to group the data on the basis of a Grouping flag. And this GroupingFlag is generated on the 0 value occurence of another column in this table called 'Price'. For EX, plz refer below table

Product Price GroupingFlag
P1 -10 1
P1 -10 2
P2 10 3
P2 20 4
P2 0 4
P3 20 5
P3 -15 6
P3 0 6
P3 0 6
P3 0 6
Now, on the basis of 0 occurrence in Price value, Groupingflag value is set. first row of price is -10, hence Groupingflag is marked as 1, in second row again this is non zero, hence Grouping flag is marked as 2. This goes on till fourth row, where 0 appears now, Groupingflag should retain the previous occurrence of GroupingFlag value i.e.4.

SO I need to populate this GroupingFlag column in a table which has huge data. I have achieved this with the help of While/For Loop but these are taking considerable amount of item. Is there any approach where we can do this, by single update statement or select statement. I have tried to implement it using below Update query, however with huge data this is giving abrupt results but working for less data.

DECLARE @runningValue BIGINT = 0

UPDATE #cteILIDetails SET GroupingFlag = @runningValue, @runningValue = CASE WHEN Price IS NULL OR Price <> 0 THEN @runningValue + 1 ELSE @runningValue END

Any help would be highly appreciated.

Regards,

Sankalp Singhal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-30 : 05:34:54

do you've any other column to determine the order? otherwise how will you determine what value to be set for the 0 rows? ideally there should be a unique id field or datetime field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources