SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create table Del_Test_Main (uniqueId bigint identity not null,Id int,Name varchar(50),IsDelete tinyint default 0,RootPid varchar(50))

insert into  Del_Test_Main (Id,Name,IsDelete,RootPid) values (1,'Vj',0,'20190101') , 
(2,'john',0,'20190101'), (3,'lance',1,'20190101' ), (4,'kate',1,'20190101' )

Select * into Del_Test_Main_Copy From Del_Test_Main

Create table Del_Test_Stg (Id int)

insert into Del_Test_Stg (Id) values (1) ,(3)

SELECT main.*, del.*,
    Case when del.Id IS NULL And main.IsDelete = 0  then 'Change IsDelete to 1'  
        when main.IsDelete = 1 and del.Id IS NOT NULL then 'Change IsDelete to 0' 
        Else 'Keep IsDelete as it is  ' End 
    AS mainId,  
    Case when  del.Id IS NULL And main.IsDelete = 0 then 'Change RootPid to 20190202' 
		When main.IsDelete = 1 and del.Id IS NOT NULL then 'Change RootPid to 20190202'
        Else 'Keep RootPid as it is  ' End 
    AS RootPid 
--UPDATE main 
--SET IsDelete = Case when del.Id IS NULL And main.IsDelete = 0  then 1
--          when main.IsDelete = 1 and del.Id IS NOT NULL then 0 
--          Else IsDelete End,
--		RootPid = Case when del.Id IS NULL And main.IsDelete = 0  then '20190202'
--          when main.IsDelete = 1 and del.Id IS NOT NULL then '20190202' 
--          Else RootPid End
FROM Del_Test_Main main
LEFT JOIN Del_Test_Stg del
ON main.Id=del.Id

UPDATE main 
SET IsDelete = Case when del.Id IS NULL And main.IsDelete = 0  then 1
          when main.IsDelete = 1 and del.Id IS NOT NULL then 0 
          Else IsDelete End,
		RootPid = Case when del.Id IS NULL And main.IsDelete = 0  then '20190202'
          when main.IsDelete = 1 and del.Id IS NOT NULL then '20190202' 
          Else RootPid End
FROM Del_Test_Main main
LEFT JOIN Del_Test_Stg del
ON main.Id=del.Id

Select * from Del_Test_Main_Copy
Select * from Del_Test_Main

uniqueId	Id	Name	IsDelete	RootPid	Id	mainId	RootPid
1	1	Vj	0	20190101	1	Keep IsDelete as it is	Keep RootPid as it is
2	2	john	0	20190101	null	Change IsDelete to 1	Change RootPid to 20190202
3	3	lance	1	20190101	3	Change IsDelete to 0	Change RootPid to 20190202
4	4	kate	1	20190101	null	Keep IsDelete as it is	Keep RootPid as it is

uniqueId	Id	Name	IsDelete	RootPid
1	1	Vj	0	20190101
2	2	john	0	20190101
3	3	lance	1	20190101
4	4	kate	1	20190101

uniqueId	Id	Name	IsDelete	RootPid
1	1	Vj	0	20190101
2	2	john	1	20190202
3	3	lance	0	20190202
4	4	kate	1	20190101