COUNTER - 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

SQL Server Development (2000)

COUNTER

Author

Topic

Freddie
Starting Member

29 Posts

Posted - 2006-04-26 : 13:25:09

Hello:

I have a column with all NULL values. I would like to fill that column with numbers can someone help?

Much appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 13:53:47

Do you want them incrementing from 1? Do you have an integer primary key? Could you post your DDL (CREATE TABLE statement) for the table and let us know which column you want to update?

Tara Kizer
aka tduggan

Freddie
Starting Member

29 Posts

Posted - 2006-04-26 : 14:08:46

Yes i would like to increment by 1 but cannot use identity key...table already loaded.

CREATE TABLE Comp
(
CustNo VARCHAR(6) NOT NULL,
CmpnyCd VARCHAR(10),
flg INT
)

flg is the null column.

thx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 14:43:58

Here you go:


SET NOCOUNT ON

DECLARE @i int, @CustNo varchar(6), @MaxCustNo varchar(6)

SET @i = 1

SELECT @CustNo = MIN(CustNo), @MaxCustNo = MAX(CustNo)
FROM Comp

WHILE @CustNo <= @MaxCustNo
BEGIN
	UPDATE Comp
	SET flg = @i
	WHERE CustNo = @CustNo

	SET @i = @i + 1

	SELECT @CustNo = MIN(CustNo)
	FROM Comp
	WHERE CustNo > @CustNo
END

Tara Kizer
aka tduggan

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-27 : 06:04:36

Here are a couple of other options...

--data
declare @Comp table (CustNo VARCHAR(6) NOT NULL, CmpnyCd VARCHAR(10), flg INT)
insert @Comp (CustNo, CmpnyCd)
          select 'a', 'b'
union all select 'c', 'd'
union all select 'g', 'h'
union all select 'i', 'j'
union all select 'e', 'f'

--calculation (option 1 - ordering not important)
declare @i int
set @i = 0
update @Comp set flg = @i, @i = @i + 1
select * from @Comp

--reset
update @Comp set flg = null

--calculation (option 2 - ordering important)
update c set flg = counter from @Comp c inner join (
    select a.CustNo, count(*) as counter from @Comp a
    inner join @Comp b on a.CustNo >= b.CustNo group by a.CustNo) d
        on c.CustNo = d.CustNo
select * from @Comp

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources