• Remembering that a CTE is just another way to code sub-queries, your update with CTE is the same as this:

    UPDATE #tReceive

    SET PoReceiveHeaderGuid = hdr.PoReceiveHeaderGuid

    FROM #tReceive tr

    INNER JOIN

    (

    SELECT NEWID() PoReceiveHeaderGuid

    , rd.JobNumberID

    , rd.PONUMBER

    , rd.DELIVNOTE

    , rd.DATE

    from

    (

    SELECT DISTINCT tr.JobNumberID

    , tr.PONUMBER

    , tr.DELIVNOTE

    , tr.DATE

    FROM #tReceive tr

    WHERE tr.PoReceiveHeaderGuid IS NULL

    ) rd

    ) hdr

    ON tr.JobNumberID = hdr.JobNumberID

    AND hdr.PONUMBER = tr.PONUMBER

    AND hdr.DELIVNOTE = tr.DELIVNOTE

    AND hdr.DATE = tr.DATE

    Both the above and your original CTE-based update should generate the same actual execution plan, since SQL just takes the CTE (since it is non-recursive) and unwinds it to a series of nested sub-queries.

    I get unique, new GUIDs every time (whether with your query or mine) which is what I would expect.

    Gerald Britton, Pluralsight courses