Update field - 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

Update field

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-02-17 : 14:45:33

I have data in a table that looks like this


ord_no   rec_type   extra
1233        O       
1233        O  
1233        O
1245        O
1245        O
1245        O

I would like to update the extra field Like this:


ord_no   rec_type   extra
1233        O        10
1233        O        20 
1233        O        30
1245        O        10
1245        O        20
1245        O        30

So every time the ord_no changes I reset to 10.

Update TableName
set extra = ?????

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-17 : 14:49:24

[code];WITH cte AS
(
SELECT *,
10*ROW_NUMBER() OVER (PARTITION BY ord_no ORDER BY (SELECT NULL)) AS RN
)
UPDATE cte SET extra = RN;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources