April 7, 2014 at 1:20 am
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_IdAgreement_Area_NameAgreement_Area_Number
1Regelsæt 1NULL
2Regelsæt 2NULL
3Regelsæt 3NULL
4Regelsæt 4NULL
5Regelsæt 5NULL
6Regelsæt 6NULL
7Regelsæt 7NULL
1009Regelsæt 8NULL
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
April 7, 2014 at 1:42 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2014 at 1:55 am
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
April 7, 2014 at 1:58 am
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;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2014 at 2:00 am
Yep, looks nice!
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply