[SQL] 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:

id col1 col2 col3
1 t1 1 Policy1
2 t3 2 Policy2
3 t4 3 Policy1

tableB:

id col1 col2
1 1 1
2 1 2
3 2 1

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.

Continue reading...