Ufdjrw

I have 4 columns in a table - 2 original, 2 new I added

O‌ne column is a document number, and a second column is the version. A document number may have multiple versions but will retain the same document number.‌

I‌ needed to be able to change all document numbers to a specific range, which was easy. I created a third column "doc2" and made another version column, retaining the same info.

I‌'m racking my brain as to how to code this?!?!

A‌ny help appreciated‌‌‌

D‌OC VERSION
1‌ 1
2‌ 1
3‌ 1
3‌ 2
3‌ 3
3‌ 4

I‌ needed to be able to change all document numbers to a specific range, which was easy. I created a third column "doc2" and made another version column, retaining the same info

D‌OC VERSION DOC2 VERSION2
1‌ 1 100 1
2‌ 1 101 1
3‌ 1 103 1
3‌ 2 104 2
3‌ 3 105 3
3‌ 4 106 4

W‌hat I want to do is update the DOC2 column to reflect the "min" docnumber of the set based on the original docnumber. For example,

D‌OC2 that has an original document number of 3 would all turn in to 103 like below

D‌OC VERSION DOC2 VERSION2
1‌ 1 100 1
2‌ 1 101 1
3‌ 1 103 1
3‌ 2 103 2
3‌ 3 103 3
3‌ 4 103 4

Maybe I'm under-thinking this, but aren't you just looking for something along this line?

UPDATE yourTable
 SET DOC2 = DOC + 99; --<Value at the bottom of your new range. 99 from the sample data. 

I see that 102 was skipped in your DOC2 sequence, so maybe there's some logic I'm not seeing.

I'm with @Eric in thinking that this is far simpler than it probably should be, but:

UPDATE yourTable
SET DOC2 = '10' + CAST(DOC AS VARCHAR)
 , Version2 = Version

Here's another option. You can use the MIN() function.

DECLARE @TestData TABLE
 (
 [DOC] INT
 , [VERSION] INT
 , [DOC2] INT
 , [VERSION2] INT
 );

INSERT INTO @TestData (
 [DOC]
 , [VERSION]
 , [DOC2]
 , [VERSION2]
 )
VALUES ( 1, 1, 100, 1 )
 , ( 2, 1, 101, 1 )
 , ( 3, 1, 103, 1 )
 , ( 3, 2, 104, 2 )
 , ( 3, 3, 105, 3 )
 , ( 3, 4, 106, 4 );


--Use MIN()
SELECT *
 , MIN([DOC2]) OVER ( PARTITION BY [DOC] ) --Give me the minimum DOC2 value for the DOC
FROM @TestData;

--You can not use a window function in a update statement
--so either a CTE
WITH [cte]
AS ( SELECT [DOC]
 , [DOC2]
 , MIN([DOC2]) OVER ( PARTITION BY [DOC] ) AS [MinDoc2]
 FROM @TestData )
UPDATE [cte]
SET [cte].[DOC2] = [cte].[MinDoc2];

--or a sub query will do the job as well
UPDATE [a]
SET [a].[DOC2] = [b].[MinDoc2]
FROM @TestData [a]
INNER JOIN (
 SELECT [DOC]
 , MIN([DOC2]) AS [MinDoc2]
 FROM @TestData
 GROUP BY [DOC]
 ) [b]
 ON [b].[DOC] = [a].[DOC];

SELECT * FROM @TestData