Solved

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

Posted on 2011-03-24
14
776 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Accepted Solution

by:
Roman Gherman earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now