August 4, 2016 at 2:12 pm
I have a tricky update to make. Any help is highly appreciated. Thanks in advance for your help.
Here is the scenario as shown on the sample data attached. I have to update P_main.ver_key from Versions.verkey where P_main.ver_key is null. The logic is to get the ver_key for that pid where Versions.pcmm<=max(P_main.vdmm). The values that should be populated in the null spots are shown in the 'after update' column on the sample data.
example:
P_main table:
pid = 50000178
vcmm= 2014027001
Versions table:
pid = 50000178
max pdmm <=2014027001 is 2014032000
therefore update ver_key = 154
Sample Data
P_main
pid vcmm ver_key after update
50000178 0 153
50000178 2014027001 NULL 154
50000178 2014032000 154
50000178 2014032000 154
50000178 2014032000 154
50000178 2014032000 154
50000705 0 767
50000705 2014154001 768
50000705 2014154001 768
50000705 2014154002 769
50000705 2014154002 769
50000705 2014260000 770
50000705 2014260000 770
50000705 2014293000 771
50000705 2014293001 NULL 772
50000705 2014354000 NULL 773
Versions
pid vdmm ver_key
50000178 0 153
50000178 2014032000 154
50000705 0 767
50000705 2014154001 768
50000705 2014154002 769
50000705 2014260000 770
50000705 2014293000 771
50000705 2014293002 772
50000705 2014354001 773
August 4, 2016 at 2:19 pm
hi...would help us all to help you if you read this please
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
and post back with the article's suggested method.
thanks
edit updated URL correctly
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 4, 2016 at 2:38 pm
the link is showing a blank page.
August 4, 2016 at 2:39 pm
dk98681 (8/4/2016)
the link is showing a blank page.
Try this:
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2016 at 2:40 pm
Phil Parkin (8/4/2016)
dk98681 (8/4/2016)
the link is showing a blank page.Try this:
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
thanks Phil.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 4, 2016 at 3:13 pm
Since you're on SQL 2012, look at LAST_VALUE.
Drew
Edited: Added hyperlink.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 4, 2016 at 3:48 pm
I think CROSS APPLY (or OUTER APPLY if you want NULL if no match is found) will do it, but of course I can't test it without useable data.
Btw, if you typically lookup on versions by ( pid ) or by ( pid, ver_key ), as this query is, you should cluster the Versions table on ( pid, ver_key ) for best overall performance.
Edit: forgot to post the actual UPDATE statement, D'OH:
UPDATE P
SET ver_key = oa1.ver_key
FROM P_main P
CROSS APPLY (
SELECT TOP (1) ver_key
FROM Versions V
WHERE V.pid = P.pid AND V.vddm <= P.ver_key
ORDER BY vddm DESC
) AS oa1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply