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 ?
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 ?
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
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
SELECT NAME,
ROW_NUMBER() OVER(ORDER BY NAME desc) as RowID
from myTable
ASKER
jimyx
update dbo.Oficios set Num = RowID
"The column name 'RowID' is invalid"
update dbo.Oficios set Num = RowID
"The column name 'RowID' is invalid"
ASKER
roma1123:
Your select works fine, but can i chain it with a Update?
Your select works fine, but can i chain it with a Update?
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"
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"
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"
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry Veljean, did not get your point at the beginning.
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
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
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
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
ASKER
Thanks!
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