A slightly more compact version:UPDATE a
SET a.source='CP',
    a.PREVENTCOPY = L.PREVENTCOPY,
    a.PREVENTPRINT = L.PREVENTPRINT,
    a.ISAMOVAR = L.ISAMOVAR,
    a.ID = L.ID,
    a.MREADONLY = L.MREADONLY,
    a.FIELDLEN = L.FIELDLEN,
    a.EDITTYPE = L.EDITTYPE,
    a.FIELDTITLE = L.FIELDTITLE,
    a.MEMOTITLE = L.MEMOTITLE,
    a.TITLE = L.TITLE,
    a.HELP = L.HELP,
    a.COMMENTS = L.COMMENTS,
    a.RESERVED = L.RESERVED,
    a.FIELDFORMAT = L.FIELDFORMAT,
    a.FIELDDATA = L.FIELDDATA,
    a.FIELDVALUE = L.FIELDVALUE,
    a.LASTUSERID = L.LASTUSERID,
    a.tag = L.tag
FROM CR.[Landing] AS L
    INNER JOIN (
                SELECT PREVENTCOPY, , ISAMOVAR, ID, MREADONLY, FIELDLEN, EDITTYPE, FIELDTITLE, MEMOTITLE, TITLE, HELP,
                    COMMENTS, RESERVED, FIELDFORMAT, FIELDDATA, FIELDVALUE, LASTUSERID, tag, [source], fieldname, pcfile
                FROM CR.[Landing]
                WHERE PCFILE LIKE '%Road%'
                ) AS a
        ON L.FIELDNAME= a.FIELDNAME
        AND REPLACE(L.pcfile, 'CAR', '') = REPLACE(LEFT(a.pcfile, LEN(a.pcfile) - 1), 'Road', '')
WHERE REPLACE(L.pcfile, 'CAR', '') IN
    (
    SELECT DISINCT REPLACE(LEFT(pcfile, LEN(pcfile) - 1), 'Road', '')
    FROM CR.[Landing]
    WHERE PCFILE LIKE 'Road%'
    );