Link to home
Start Free TrialLog in
Avatar of Veljean
Veljean

asked on

Update a column in SQL 2005 table's , using RowID

Hi

I would like to update a column in my table (SQL server 2005) using the RowID of each record

ROWID  NAME
1        record1
2        record2
3        record3
4        record4

how should be the correct UPDATE ?
Avatar of knightEknight
knightEknight
Flag of United States of America image

 with v as (
    select 1 as rowid, T.Name
    from myTable
    union all
    select v.rowid+1,  v.Name
    from v
)

update myTable
set myRowIDColumn = v.rowid
from myTable T
join v V
  on V.NAME = T.NAME
Avatar of jimyX
jimyX

Which records you are targeting here?

To update all the records you use:
Update Table1 set Name = 'WhatEver'+RowID
Result:
ROWID     NAME
1           WhatEver1
2           WhatEver2
3           WhatEver3
4           WhatEver4


To limit the update to the records with RowID greater than 3:
Update Table1 set Name = 'WhatEver'+RowID where RowId > 3
Result:
ROWID     NAME
1             record1
2             record2
3             record3
4             WhatEver4

Less than 3:
Update Table1 set Name = 'WhatEver'+RowID where RowId < 3
Result:
ROWID     NAME
1           WhatEver1
2           WhatEver2
3           record3
4           record4
USE THIS to get the correct rownumber in MS SQL


SELECT NAME,
ROW_NUMBER() OVER(ORDER BY NAME desc) as RowID
from myTable
Avatar of Veljean

ASKER

jimyx

update dbo.Oficios set Num = RowID

"The column name 'RowID' is invalid"
Avatar of Veljean

ASKER

roma1123:

Your select works fine, but can i chain it with a Update?
Avatar of Veljean

ASKER

knightEknight:

update dbo.Oficios
set myRowIDColumn = v.rowid
from dbo.Oficios T
join v V
  on V.ID = T.ID

"the object 'v' is not valid"
Avatar of Veljean

ASKER

with v as (
    select 1 as rowid, T.ID
    from dbo.Oficios
    union all
    select v.rowid+1,  v.ID
    from v
)




update dbo.Oficios
set myRowIDColumn = v.rowid
from dbo.Oficios T
join v V
  on V.ID = T.ID


"The identificator "T.ID"" cannot be linked"
ASKER CERTIFIED SOLUTION
Avatar of Roman Gherman
Roman Gherman
Flag of Moldova, Republic of image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry Veljean, did not get your point at the beginning.
Avatar of Veljean

ASKER

thanks roma1123, the final query is like this

WITH OrderedOficios AS
(
    SELECT ID,
    ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'
    FROM dbo.Oficios
)
Update dbo.Oficios  
SET Num = OrderedOficios.RowNumber From OrderedOficios where Oficios.ID = OrderedOficios.ID







Sorry, forget my first post -- roma1123 is correct:



with v as (
  SELECT NAME,
  ROW_NUMBER() OVER(ORDER BY NAME asc ) as RowID
  from myTable
)

update myTable set myRowIDColumn = RowID
-- select *
from v
join myTable T
  on T.Name = V.Name

Avatar of Veljean

ASKER

just a final question... since i want to do this in a webpage, should I close the table OrderedOficios ? it not remains in memory or something?
Should not be a problem
Avatar of Veljean

ASKER

Thanks!