I don't really understand what's the deal with TEMPLATE_NAME in your LiveSQL example. That column isn't in any of your tables!
Anyway, you set column values using a subquery. You can place many of these inside coalesce. Oracle Database will then return the value from the first subquery that joins to the outer table. And you can pass a default if none of them do.
create table test1(sno number,old Varchar2(15),new varchar2(15),org varchar2(10),entity varchar2(15));
create table test2(sno number,name Varchar2(15),org varchar2(10),entity varchar2(15),err_msg varchar2(200));
create table test3(sno number,name Varchar2(15),org varchar2(10),entity varchar2(15),err_msg varchar2(200));
insert all
into test2 values (1,'Donald','ERP','PD',NULL)
into test2 values (2,'Obama','ERP','PD',NULL)
into test2 values (3,'Hillary','ERP','PD',NULL)
into test2 values (4,'John','ERP','PD',NULL)
into test2 values (5,'Hoper','ERP','PD',NULL)
select * from dual;
insert all
into test1 values (1,'Donald','Trump','ERP','PD')
into test1 values (2,'Obama','Barack','ERP','PD')
into test1 values (3,'Hillary','Clinton','ERP','PD')
into test1 values (4,'John Hoper','JHoper','ERP','PD')
into test1 values (5,'TIM','T Martin','ERP','PD')
select * from dual;
alter table test3 drop column err_msg;
insert all
into test3 values (1,'Will Smith','ERP','PD')
into test3 values (2,'Tiger Woods','ERP','PD')
into test3 values (3,'Modi','ERP','PD')
into test3 values (4,'John','ERP','PD')
into test3 values (5,'TIM','ERP','PD')
select * from dual;
select * from test2;
SNO NAME ORG ENTITY ERR_MSG
1 Donald ERP PD
2 Obama ERP PD
3 Hillary ERP PD
4 John ERP PD
5 Hoper ERP PD
update test2 t2
set err_msg = coalesce( (
select t1.new from test1 t1
where t1.old = t2.name
and t1.org = t2.org
and t1.entity = 'PD'
) , (
select t3.name from test3 t3
where t3.name = t2.name
), 'Template Not found in System' );
select * from test2;
SNO NAME ORG ENTITY ERR_MSG
1 Donald ERP PD Trump
2 Obama ERP PD Barack
3 Hillary ERP PD Clinton
4 John ERP PD John
5 Hoper ERP PD Template Not found in System It looks like you want to set two separate columns. But I don't really understand exactly how. But the process above should help you.