Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update a variable with row numbers Expand / Collapse
Author
Message
Posted Monday, April 7, 2014 1:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
Hi All:

I have this pretty simple table:

CREATE TABLE [dbo].[Agreement_Area](
[Agreement_Area_Id] [int] IDENTITY(1,1) NOT NULL,
[Agreement_Area_Name] [varchar](50) NULL,
[Agreement_Area_Number] [int] NULL,
CONSTRAINT [PK_Agreement_Area] PRIMARY KEY CLUSTERED
(
[Agreement_Area_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Which at the time beeing has the values:

Select * from dbo.Agreement_Area

Agreement_Area_Id Agreement_Area_Name Agreement_Area_Number
1 Regelsæt 1 NULL
2 Regelsæt 2 NULL
3 Regelsæt 3 NULL
4 Regelsæt 4 NULL
5 Regelsæt 5 NULL
6 Regelsæt 6 NULL
7 Regelsæt 7 NULL
1009 Regelsæt 8 NULL

What i want is to update the NULL's with row numbers, i.we , iD 1 = 1 to ID 1009 = 8

I have to update +100 databases with the field Agreement_Area_Number, so just to do it manually is not an option.

Best regards

Edvard Korsbæk



Post #1558939
Posted Monday, April 7, 2014 1:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 12,956, Visits: 10,727
The UPDATE statement itself is pretty simple using the ROW_NUMBER function.
Maybe you can create some dynamic SQL: loop over the different tables, insert the table name in the update statement and execute the statement.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558942
Posted Monday, April 7, 2014 1:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
The function ROW_NUMBER was new to me. Thanks.

SELECT ROW_NUMBER() OVER(ORDER BY Agreement_Area_Id DESC) AS Row from dbo.Agreement_Area

Gives me the row numbers.

But to set Agreement_Area_Number = ROW_NUMBER() OVER(ORDER BY Agreement_Area_Id DESC) in an update statement is not legal.

So still - How to?

Best regards

Edvard Korsbæk
Post #1558943
Posted Monday, April 7, 2014 1:58 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 12,956, Visits: 10,727
This should do the trick.

WITH CTE_rownumbers AS
(
SELECT Agreement_Area_Id, RID = ROW_NUMBER() OVER(ORDER BY Agreement_Area_Id DESC)
FROM [dbo].[Agreement_Area]
)

UPDATE aa
SET Agreement_Area_Number = cte.RID
FROM [dbo].[Agreement_Area] aa
JOIN CTE_rownumbers cte ON aa.Agreement_Area_Id = cte.Agreement_Area_Id;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558944
Posted Monday, April 7, 2014 2:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:12 AM
Points: 91, Visits: 158
Yep, looks nice!

Thanks!
Post #1558945
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse