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)





10 rows affected

update m
    set isdelete = (case when s.id is null then 1
                         when s.id is not null and m.isDelete = 1 then 0
                         else isdelete
                    end)
from del_test_main m left join
     del_test_stg s
     on m.id = s.id
where s.id is null or s.id is not null and m.isDelete = 1;
3 rows affected

select *
from del_test_main


uniqueId
Id
Name
IsDelete
RootPid


1
1
Vj
0
20190101

2
2
john
1
20190101

3
3
lance
0
20190101

4
4
kate
1
20190101