Update SQL table with incremental numbers per row 1

[koolestj]

I have this script written and when I run it I get
Server Msg 512, Level 16, State 1, LIne 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=......

The script is as follows
Use VRSDB
GO Declare @scounter smallint
SET @scounter=0
WHILE (select sitenumber from sites)>1
BEGIN
UPDATE sites
SET sitenumber=1+@sitecounter
SET @sitecounter=@scounter+1
CONTINUE
END

@scounter is my counter to increment the sitenumber (field) to update each row.

Any help would be appreciated.
Thanks.

 

[maswien]

The problem is
(select sitenumber from sites) is not supposed to be return one value, so it's wrong, I don't know what's the rule you want update the table, can you post that?

 

[koolestj]

I'm not an expert. What do you mean by rule?
I just want to put an autonumber for 1347 records in the sites table. the sitenumber field currently has 1-value on all records. I turned off the unique key constraint on the table.
Thanks.

 

[ESquared]

DECLARE @scounter int
SET @scounter = [First sitenumber desired - 1]

UPDATE sites
   SET
      @scounter = scounter +1,
      sitenumber = @scounter
   WHERE sitenumber > 0

Just make sure you seed the scounter number properly and put the right conditions in the WHERE clause.

 

[koolestj]

Thanks much Esquared. After looking at your code this is what I wrote and the results were what I was looking for to happen.

DECLARE @scounter int
SET @scounter = 0

UPDATE sites
SET
@scounter = @scounter +1,
sitenumber = @scounter
WHERE sitenumber > 0