Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-24
14
Medium Priority
?
790 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

670 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