Geek asks and answers

Use the case when an expression for updating from two tables

Is it possible to update a table column from two optional tables in Oracle. I mean, there are 2 tables whose columns are exactly the same, and according to the input ID, I want to update the linked table. For instance,

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 have exactly the same columns with different records.

Thank.

+3
source share
3 answers

START UPDATE TABLE_A STATUS SET = '0' WHERE A.ID = # ID #; if sql% notfound then UPDATE Table_B B SET STATUS = '0' WHERE B.ID = # ID #; END IF; END

0
source

I don't think this is possible with one status, but you can achieve it with 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;

+1
source

, . , , , , , "1 " 1 "" .

In this case, you can simply update both tables. If the identifier does not exist, nothing will be updated. This can be as fast as first selecting by id from table a, and then updating either a or b. You can also use the pl / sql block to improve performance. (If necessary, he will execute only the second expression)

create or replace procedure update_table(p_id in table_a.id%type)
as
begin
    update table_a
    set    status = 0
    where  id = p_id;

    -- only update table_b if table_a didn't update anything
    if sql%rowcount == 0 then
        update table_b
        set    status = 0
        where  id = p_id;
    end if;
end;
0
source

More articles:


All Articles