Using Case When Statement For Updating from Two Tables

Question

Is it possible to update a column of a table from two optional tables, in Oracle. I mean, there are 2 tables whose columns are exactly same and, according to input Id, I want to update related table. For eg.

UPDATE CASE WHEN EXISTS (
    SELECT A.ID FROM Table_A A 
    WHERE  A.ID = 'B1'
    )
    THEN
      Table_A A 
      SET A.Status = '0'
      WHERE A.ID = 'B1'
    ELSE
      Table_B B
      SET B.Status = '0'
      WHERE B.ID = 'B1'

Where Table_A and Table_B has exactly same columns with different records.

Thank you.

Answer 1

I don't think it's possible with a single statment, but you could achieve it using PL/SQL:

declare
VarCount number;

begin

SELECT  COUNT(*)
INTO    VarCount
FROM    Table_A
WHERE   ID = 'B1';

IF  VarCount > 1  THEN
    UPDATE  Table_A
    SET     Status = '0'
    WHERE   ID = 'B1';
ELSE
    UPDATE  Table_B
    SET     Status = '0'
    WHERE   ID = 'B1';
END IF;

end;