help needed with sql update

Question

Post reply

help needed with sql update

dk98681

SSC-Addicted

Points: 497
More actions
August 4, 2016 at 2:12 pm

#312283

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
pidvcmm ver_keyafter update
500001780 153
500001782014027001NULL 154
500001782014032000154
500001782014032000154
500001782014032000154
500001782014032000154
500007050 767
500007052014154001768
500007052014154001768
500007052014154002769
500007052014154002769
500007052014260000770
500007052014260000770
500007052014293000771
500007052014293001NULL 772
500007052014354000NULL 773
Versions
pidvdmm ver_key
500001780 153
500001782014032000154
500007050 767
500007052014154001768
500007052014154002769
500007052014260000770
500007052014293000771
500007052014293002772
500007052014354001773

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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

dk98681 SSC-Addicted Points: 497 More actions · Answer 2

dk98681

SSC-Addicted

Points: 497

August 4, 2016 at 2:38 pm

#1893951

the link is showing a blank page.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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]

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

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

drew.allen SSC Guru Points: 76985 More actions · Answer 5

Since you're on SQL 2012, look at LAST_VALUE.

Drew

Edited: Added hyperlink.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".