autonumbering existing records through SQL update

[cousinconley]

I have an existing table with records. A new field was added through a table alter. This field is a new id field to uniquely identify records. Someone previously had created this table but used a couple of text fields for keys. I added a numberic field where all the values in it currently are 0. I created a SQL update to attempt an incrementation of one based on the existing maximum value.

Below is my attempt but all it did was change all values from 0 to 1.

UPDATE PS_ZPA_CNE_NEWS
SET ZPA_CNE_NEWS_ID = ((SELECT MAX(ZPA_CNE_NEWS_ID) FROM PS_ZPA_CNE_NEWS)+1)

Can anyone suggest an update statement that would work?

Thanks,

Richard

 

[SQLDenis]

make the field an identity field

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/