Update a SQL table with values from another nested query

I am currently using a SQL Server Agent job to create a master user table for my in-house web applications, pulling data from 3 other databases; Sharepoint, Practice Management System and Our HR Database.

Currently it goes...

truncate table my_tools.dbo.tb_staff
go
insert into my_tools.dbo.tb_staff 
 (username
 ,firstname
 ,surname
开发者_开发知识库 ,chargeoutrate)

    select right(wss.nt_user_name,
  ,hr.firstname
  ,hr.surname
  ,pms.chargeoutrate

    from sqlserver.pms.dbo.staff as pms

    inner join sqlserver.wss_content.dbo.vw_staffwss as wss
 on pms.nt_user_name = wss.nt_user_name

    inner join sqlserver.hrdb.dbo.vw_staffdetails as hr
 on wss.fullname = hr.knownas
go

The problem is that the entire table is cleared as the first step so my auto increment primary key/identified on tb_staff is certain to change. Also if someone is removed from sharepoint or the PMS they will not be recreated on this table and this will cause inconsistencies throughout the database.

I want to preserve entries in this table, even after they are removed from one of the other systems.

I suppose what I want to do is: 1) Mark all exiting entries in tb_staff as inactive (using a column called active and set it to false) 2) Run the query on the three joined tables and update every found record, also marking them as active.

I can't see how I can nest a select statement within an Update statement like I have here with the Insert statement.

How can I achieve this please?

*please note I have edited my SQL down to 4 columns and simplified it so small errors are probably due to rushed editing. The real query is far bigger.

WITH source AS(
SELECT RIGHT(wss.nt_user_name, 10) nt_user_name, /*Or whatever - this is invalid in the original SQL*/
       hr.firstname,
       hr.surname,
       pms.chargeoutrate
FROM   staff AS pms
       INNER JOIN vw_staffwss AS wss
         ON pms.nt_user_name = wss.nt_user_name
       INNER JOIN vw_staffdetails AS hr
         ON wss.fullname = hr.knownas  
)
MERGE 
        INTO tb_staff
        USING source
        ON source.nt_user_name= tb_staff.username /*Or whatever you are using as the key */
          WHEN MATCHED 
            THEN UPDATE SET active=1  /*Can synchronise other columns here if needed*/
        WHEN NOT MATCHED BY TARGET 
            THEN INSERT (username, firstname, surname, chargeoutrate, active) VALUES (nt_user_name,firstname, surname, chargeoutrate, 1)
        WHEN NOT MATCHED BY source 
            THEN UPDATE SET active=0;