UPDATE clause problem - 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

General SQL Server Forums

New to SQL Server Programming

UPDATE clause problem

Author

Topic

TorspeR
Starting Member

11 Posts

Posted - 2010-09-22 : 04:20:13

Hello,
I have a problem with UPDATE clause. I have a list of users and now I would like to make them an unique number per user. If I use UPDATE clause, what should I do if I want to start the numbers of the column from 1 (row 1) and the next (row 2) would be 1 greater than the previous. I mean 1,2,3, .... Thanks!

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:28:29

you mean to say:

in table tbl_xyz you are having users like:

[user]
user1
user2
user3
user4
user5
user6

and after UPDATE ?? you want something like this
[user] [uniqueId]
user1 1
user2 2
user3 3
user4 4
user5 5
..
..

Is it so?

Kristen
Test

22859 Posts

Posted - 2010-09-22 : 04:29:50

Can you add an column to the table with IDENTITY property? That will automatically assign a unique ID to all existing rows (and to any new rows that are inserted in the future)

Kristen
Test

22859 Posts

Posted - 2010-09-22 : 04:31:29

If you want to just assign a number to existing rows here's what I would do


SELECT [T_ID] = IDENTITY(int, 1, 1),
       [T_MyPK] = MyPK
INTO #TEMP
FROM MyTable
ORDER BY ... columns controlling ID ordering ...

UPDATE U
SET MyID_Column = T_ID
FROM MyTable AS U
     JOIN #TEMP
         ON T_MyPK = MyPK

TorspeR
Starting Member

11 Posts

Posted - 2010-09-22 : 04:31:41

Yes, thats right!

So I guess there is a way to make this with UPDATE after I've made the uniqueid column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources