October 8, 2015 at 7:44 am
Folks,
The following works just fine. However I need one more piece of help. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
October 8, 2015 at 8:08 am
without sample data or table definition it makes it a little more difficult to provide answer. Please read link in my signature for how to post to get better answers.
that being said, something like this should get you what you are looking for.
CREATE TABLE [tmpMHPCLMDET] ([ServiceLineID] int,
[ADMTDT] varchar(8));
INSERT INTO [tmpMHPCLMDET]
VALUES ('0','20150101'),
('0','20150201'),
('0','20150301'),
('0','20150701'),
('0','20150601'),
('0','20150501');
IF EXISTS ( SELECT [c].[name]
FROM [sys].[columns] AS [c]
WHERE object_id = OBJECT_ID('tmpMHPCLMDET')
AND [C].[name] = 'ServiceLineID' )
BEGIN
--Adding a sequence of numbers to the ServiceLineID column.
WITH cte
AS (SELECT [ServiceLineID],
[ADMTDT],
ROW_NUMBER() OVER (ORDER BY [ADMTDT] ASC) AS [rn]
FROM [tmpMHPCLMDET])
UPDATE [cte]
SET [ServiceLineID] = [rn];
END;
October 8, 2015 at 8:55 am
You've got basically the same answer twice in different threads. Have you tried it?
This was the original thread: http://www.sqlservercentral.com/Forums/Topic1726364-2799-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply