Variable update - 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

General SQL Server Forums

New to SQL Server Programming

Variable update

Author

Topic

AlanPD
Starting Member

2 Posts

Posted - 2012-05-08 : 07:24:51

I want to write an update query to add a reference based on the date of an order item. So that all Items with the earliest date for that order have a reference of 1, items with the next date are 2 and so on.
Eg.
Order, - Item, - Date, ---- Reference
0001 --- 01 ---- 01/01/01 - 1
0001 --- 02 ---- 01/01/01 - 1
0001 --- 03 ---- 01/02/01 - 2
0002 --- 01 ---- 01/03/01 - 1

Is it possible to create an update statement which iterates through the records setting a values which is determined by the values in previous records?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-08 : 07:31:40

You can use RANK or DENSE_RANK function - probably in your case you need DENSE_RANK.

SELECT
	*,
	DENSE_RANK() OVER(PARTITION BY ORDER ORDER BY [Date]) AS Reference
FROM
	YourTable

If you already have a column in your table named Reference that is null now and want to update that column, you can do it like this:

;with cte as
(
	SELECT
		*,
		DENSE_RANK() OVER(PARTITION BY ORDER ORDER BY [Date]) AS NewReference
	FROM
		YourTable
)
UPDATE cte SET Reference = NewReference;

AlanPD
Starting Member

2 Posts

Posted - 2012-05-08 : 09:05:40

Excellent answer, thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:20:24

Using Apply operator


UPDATE t
SET Reference = COALESCE(t1.Cnt,0) + 1
FROM YourTable t
OUTER APPLY (SELECT COUNT(DISTINCT Date) AS Cnt
             FROM YourTable
             WHERE Order= t.Order
             AND Date < t.Date
             )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources