-- Actualizar nuevos campos creados con los ids de te-sis
UPDATE A SET
A.CMD_ColaboradorTesis = B.CMD_ColaboradorTesis,
A.CMD_CiaTesis = B.CMD_CiaTesis,
A.CMD_RamoTesis = B.CMD_RamoTesis,
A.CMD_TramitadorTesis = B.CMD_TramitadorTesis,
A.CMD_ClienteTesis = B.CMD_ClienteTesis
FROM asurnet.dbo.CAMBIOMEDIACION A
INNER JOIN (
    SELECT A.CMD_Id, B.IDTSS AS CMD_ColaboradorTesis, C.IDTSS AS CMD_CiaTesis,
    D.IDTSS AS CMD_RamoTesis, E.IDTSS AS CMD_TramitadorTesis, F.IDTSS AS CMD_ClienteTesis 
    FROM asurnet.dbo.CAMBIOMEDIACION A
    LEFT JOIN tssseg0001.dbo.IDTraspasos B
    ON A.CMD_Colaborador = B.IDTras AND B.TableID = 'Colaboradores'
    LEFT JOIN tssseg0001.dbo.IDTraspasos C
    ON A.CMD_Cia = C.IDTras AND C.TableID = 'Companias'
    LEFT JOIN tssseg0001.dbo.IDTraspasos D
    ON A.CMD_Ramo = D.IDTras AND D.TableID = 'Ramos'
    LEFT JOIN tssseg0001.dbo.IDTraspasos E
    ON A.CMD_Tramitador = E.IDTras AND E.TableID = 'Usuarios'
    LEFT JOIN tssseg0001.dbo.IDTraspasos F
    ON A.CMD_Cliente = F.IDTras AND F.TableID = 'Clientes'
) B ON A.CMD_Id = B.CMD_Id
