Easy question - 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 2000 Forums

Transact-SQL (2000)

Easy question

Author

Topic

Crespo24
Village Idiot

144 Posts

Posted - 2002-12-30 : 09:44:24

Salute,

Say you have a table of TWO columns....

MEMBER_KEY | SURNAME

now there are 250 surnames but the MEMBER_KEY column is empty.. how do you populate the MEMBER_KEY column with values from 1 to 250 ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-30 : 10:05:39

I would add an INDENTITY column to the table, then do an update to copy the values from the new ID column to the MEMBER_KEY column, and then remove the IDENTITY column.

- Jeff

Crespo24
Village Idiot

144 Posts

Posted - 2002-12-30 : 11:49:11

quote:

I would add an INDENTITY column to the table, then do an update to copy the values from the new ID column to the MEMBER_KEY column, and then remove the IDENTITY column.

- Jeff

Thank you for your help. That makes sense.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-30 : 12:24:48

I guess it doesn't matter which surname gets which member_key ...

If it where me and there aren't any duplicate surnames (that wasn't clear), I would ...


update s
set member_key = (
    select count(*)
    from sometable
    where surname < s.surname )
from sometable s

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-30 : 15:32:43

set rowcount 1
while exists (select * from tbl where MEMBER_KEY is null
update tbl
set MEMBER_KEY = (select coalesce(max(MEMBER_KEY),0) + 1 from tbl)
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-30 : 16:13:12

nr --

that is a very clever solution! quite neat. Not sure how efficient it is, but a very cool way to generate a sequence of numbers for a table; and it doesn't rely on unique records ...

- Jeff

Crespo24
Village Idiot

144 Posts

Posted - 2002-12-31 : 05:45:10

quote:

set rowcount 1
while exists (select * from tbl where MEMBER_KEY is null
update tbl
set MEMBER_KEY = (select coalesce(max(MEMBER_KEY),0) + 1 from tbl)
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yes... that is very good indeed... never thought about that... been playing around with different methods, but this one beats mine any day. You're a star nr (even though I hate you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-01 : 07:55:00

quote:
Originally posted by nr

set rowcount 1
while exists (select * from tbl where MEMBER_KEY is null
update tbl
set MEMBER_KEY = (select coalesce(max(MEMBER_KEY),0) + 1 from tbl)
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I know this is very old topic
But your method leads to endless loop
With some changes, this is workable solution


while exists (select * from tbl  where MEMBER_KEY is null)
Begin
	set rowcount 1
	update tbl 
	set MEMBER_KEY = (select coalesce(max(MEMBER_KEY),0) + 1 from tbl  )
	where MEMBER_KEY is null
	set rowcount 0
End

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-01 : 13:22:13

Here's a further option...

--data
declare @t table (MEMBER_KEY int, SURNAME varchar(30))
insert @t (SURNAME)
          select 'Smith'
union all select 'Wang'
union all select 'De Silva'
union all select 'Schmidt'
union all select 'Murphy'
union all select 'Sato'
union all select 'De Jong'
union all select 'Hansen'
union all select 'Tremblay'
union all select 'Nguyen'

--calculation
declare @i int
update @t set @i = isnull(@i, 0) + 1, MEMBER_KEY = @i
select * from @t

/*results
MEMBER_KEY  SURNAME                        
----------- ------------------------------ 
1           Smith
2           Wang
3           De Silva
4           Schmidt
5           Murphy
6           Sato
7           De Jong
8           Hansen
9           Tremblay
10          Nguyen
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-01 : 15:55:24

quote:
Originally posted by RyanRandall

Here's a further option...

See THAT is clever

, and proberly very fast, especially if you want to have the numbering depending on an ORDER BY.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources