How to update a single row from multiple rows using specific business logic?

I have 2 tables namely tableA, and tableB with primary key of tableA exist as foreign key in tableB.The business logic is to join both the tables and get records from tableB. Now if there are more than 1 record, then no logic will be applied. But, if there is only 1 record and value of a specific column of tableB is "1", then only I need to update tableA.

How can I write the update query based on the above conditions? Please help.

I have 2 tables namely tableA, and tableB with primary key of tableA exist as foreign key in tableB.

tableA:

tableB:

select * from tableB a join tableA b on a.id=b.col1 where a.col3='Policy1'

If the above query gives 2 records from tableB, then there will be no update statement. However, if the query is like:

select * from tableB a join tableA b on a.id=b.col1 where a.col3='Policy2'

Then it will give only 1 record from tableB where value of "col2" will be "1". At such case, I have to update tableA and set the value of col2 of tableA to "0" for that single row only. So, whenever, I will get only 1 row from tableB and value of "col2" will be 1, at that time only I need to update tableA.

I tried to use PLSQL but as I am new to PLSQL, I couldnot do that.

declare
v number;
p varchar;
begin
select * into v from tableB a join tableA b on a.id=b.col1 where a.col3= p
if v=1 then
update tableA
end;

How can I write the update query based on the above conditions? Please help.

You can join your tables, count rows and use the query with MERGE INTO:

--      S a m p l e    D a t a :
Create Table tableA AS
Select 1 as ID, 't1' as COL1, 1 as COL2, 'Policy1' as COL3 From Dual Union All
Select 2,   't3',   2,  'Policy2' From Dual Union All
Select 3,   't4',   3,  'Policy1' From Dual;

Create Table tableB AS
Select 1 as ID, 1 as COL1, 1 as COL2 From Dual Union All
Select 2,   1,  2 From Dual Union All
Select 3,   2,  1 From Dual;

Select * From tableA;

... tableA before update

--      multiple rows - no update  
MERGE INTO tableA t
USING
    ( Select a.ID as A_ID, b.ID as B_ID, Count(a.ID) Over(Partition By a.ID) as CNT
      From       tableA a
      Inner Join tableB b ON(b.COL1 = a.ID)
      Where a.COL3 = 'Policy1'
   ) x ON(x.A_ID = t.ID And x.CNT = 1)
WHEN MATCHED THEN UPDATE
     SET t.COL3 = 'Updated';

... check tableA

Select * From tableA;

... tableA after attempted update with multiple rows - there are no updated rows

-- single row - do the update

MERGE INTO tableA t
USING
    ( Select a.ID as A_ID, b.ID as B_ID, Count(a.ID) Over(Partition By a.ID) as CNT
      From       tableA a
      Inner Join tableB b ON(b.COL1 = a.ID)
      Where a.COL3 = 'Policy2'
   ) x ON(x.A_ID = t.ID And x.CNT = 1)
WHEN MATCHED THEN UPDATE
     SET t.COL3 = 'Updated';

... check tableA

Select * From tableA;

... tableA after attempted update with single row - 1 row updated

fiddle