Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-24
14
Medium Priority
?
791 Views
Last Modified: 2012-08-13
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 ?
0
Comment
Question by:Veljean
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35208177
 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
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35208215
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
0
 
LVL 9

Expert Comment

by:Roman Gherman
ID: 35208276
USE THIS to get the correct rownumber in MS SQL


SELECT NAME,
ROW_NUMBER() OVER(ORDER BY NAME desc) as RowID
from myTable
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Veljean
ID: 35208304
jimyx

update dbo.Oficios set Num = RowID

"The column name 'RowID' is invalid"
0
 
LVL 1

Author Comment

by:Veljean
ID: 35208329
roma1123:

Your select works fine, but can i chain it with a Update?
0
 
LVL 1

Author Comment

by:Veljean
ID: 35208348
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"
0
 
LVL 1

Author Comment

by:Veljean
ID: 35208374
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"
0
 
LVL 9

Accepted Solution

by:
Roman Gherman earned 2000 total points
ID: 35208400
yes, you can:


USE AdventureWorks2008R2;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35208417
Sorry Veljean, did not get your point at the beginning.
0
 
LVL 1

Author Comment

by:Veljean
ID: 35208546
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







0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35208548
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

0
 
LVL 1

Author Comment

by:Veljean
ID: 35208568
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?
0
 
LVL 9

Expert Comment

by:Roman Gherman
ID: 35208704
Should not be a problem
0
 
LVL 1

Author Comment

by:Veljean
ID: 35208710
Thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question