Solved

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

Posted on 2011-03-24
14
778 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

863 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

25 Experts available now in Live!

Get 1:1 Help Now