sqlsql-serversql-server-2008

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

Solution

  • 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;