user1689992 user1689992 - 4 months ago 25
SQL Question

Tricky SQL update

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 image 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 image.

Example:

P_main
table:

pid = 50000178
vcmm = 2014027001


Versions
table:

pid = 50000178
max pdmm <=2014027001 is 2014032000


therefore

update ver_key = 154


sample data

Answer

This might not be the fastest way, but it should work:

WITH CTE AS
(
    SELECT P.*, Q.ver_key ver_key_new
    FROM P_Main P
    CROSS APPLY(SELECT TOP 1 ver_key
                FROM Versions
                WHERE pid = P.pid
                AND vdmm <= P.vcmm
                ORDER BY vdmm DESC) Q
    WHERE P.ver_key IS NULL
)
UPDATE CTE
SET ver_key = ver_key_new;