March 8, 2019 at 10:14 am
I have 4 columns in a table - 2 original, 2 new I added
One 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.
DOC 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
DOC 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
What I want to do is update the DOC2 column to reflect the "min" docnumber of the set based on the original docnumber. For example,
DOC2 that has an original document number of 3 would all turn in to 103 like below
DOC 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
I'm racking my brain as to how to code this?!?!
Any help appreciated
March 8, 2019 at 10:25 am
UPDATE a
SET a.DOC2 = b.DOC2
FROM myTable a
CROSS APPLY(SELECT TOP(1) b.DOC2 FROM myTable b WHERE b.DOC = a.DOC ORDER BY b.VERSION) b
March 8, 2019 at 10:37 am
thank you, I appreciate it! Looks like I am making progress with TSQL as my logic was syntax was just a little wrong - thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply