how to set an empty field to the count of another

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)

how to set an empty field to the count of another

Author

Topic

martormg
Starting Member

2 Posts

Posted - 2015-04-09 : 13:21:45

I have a table of phone numbers such as:
create table #temp (area int, prefix int, last4 int, [count] int)
insert into #temp values
(111,222,1234,null),
(111,333,1234,null),
(111,222,4321,null),
(111,555,4321,null),
(111,444,1234,null),
(111,555,1234,null),
(111,666,1234,null)
I want to update the table and set the [count] field to the number of times the last4 repeats while the area's match (only the prefix's are differing).

End result would be:
111,222,1234,1
111,333,1234,2
111,222,4321,1
111,555,4321,2
111,444,1234,1
111,555,1234,2
111,666,1234,3

Any help is appreciated - thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-09 : 13:53:37

You can use the ROW_NUMBER() function for this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

martormg
Starting Member

2 Posts

Posted - 2015-04-09 : 14:35:06

Awesome, thanks!
select custom31a, destinarea, destprefix_a, last4_a,
row_number() over(partition by destinarea, last4_a order by last4_a) as row
from #temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources