Creating own Unique Identifier

Hi:
This is pretty basic stuff but I forget how to do this with one update.
I want to create a key for a table. I have two fields:
TableName: MyWorkingTable
PlayerID (int)
Time (DateTime)
My_New_ID (int)
PlayerIDs might be duplicated. But a combination of PlayerID and Time
will always be unique. To begin with, "My_New_ID" values will all be
NULL.
I want to grab an ID value from another table (the max value of all
the IDs from final staging table) and start assigning 'My_New_ID's in
'MyWorkingTable' starting with a number one higher that result. So if
that value is 230, then I start assigning My_New_IDs with 231 and on up
incrementing by one until I run out of records. Then I write all these
records to the final staging table.
How do I do this?
Thanks,
KaydaKayda,
I haven't tested this, it might require adjusting the initial 1 to get
it right.
UPDATE MyWorkingTable
SET My_New_ID = 1 +
(SELECT MAX(OldId) FROM SomeOtherTable) +
(SELECT COUNT(*) FROM MyWorkingTable as T
WHERE T.PlayerID < MyWorkingTable.PlayerID
OR (T.PlayerID = MyWorkingTable.PlayerID
AND T.DateTime < MyWorkingTable.DateTime)
Roy
On 15 Feb 2006 10:40:16 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>This is pretty basic stuff but I forget how to do this with one update.
>I want to create a key for a table. I have two fields:
>TableName: MyWorkingTable
>PlayerID (int)
>Time (DateTime)
>My_New_ID (int)
>PlayerIDs might be duplicated. But a combination of PlayerID and Time
>will always be unique. To begin with, "My_New_ID" values will all be
>NULL.
>I want to grab an ID value from another table (the max value of all
>the IDs from final staging table) and start assigning 'My_New_ID's in
>'MyWorkingTable' starting with a number one higher that result. So if
>that value is 230, then I start assigning My_New_IDs with 231 and on up
>incrementing by one until I run out of records. Then I write all these
>records to the final staging table.
>How do I do this?
>Thanks,
>Kayda