How can I update a view with columns that is derived from case statement?

Question

I created a view using this (neglected other columns):

create view view_table (is_root) as select
  case when C1 = 'Yes' then 1
       when C1 = 'No' then 0
       else 0
  end as is_root
  from remote_db_table

But when I tried to update the view using:

update view_table set is_root=1

It does not work. Any way to do it? The view is in Oracle but the remote table is in mySQL

Answer 1

You can do this in Oracle by building an INSTEAD OF trigger on the view.

create or replace trigger vt_instead
    instead of update on view_table
begin
    if :new.is_root != :old.is_root
    then
        update remote_db_table
        set C1 = case when :new.is_root = 1 then 'Yes'
                      else 'No' end
        where id = :new.id;
    end if;
end;
/

This will allow you to issue an UPDATE against the view which will propagate changes to the underlying view:

update view_table
set is_root = 0
where id = 23
/

I haven't tested this against a remote table on a MySQL database (not having such a set-up to hand), but if you can update the table remotely in SQL*Plus then the trigger should work as well.

---

" I want to update all the 40 columns, but only one columns is not updatable like above"

I'm afraid you're still not making completely yourself clear but what I think you're asking is how to handle columns which don't require translation.

The INSTEAD OF trigger fires instead of the triggering DML statement (the clue is in the name). That means you need to handle all the columns which you want to be updated through the view. In your scenario that would be all forty columns. The revised trigger code might look like this:

create or replace trigger vt_instead
    instead of update on view_table
begin
    update remote_db_table
    set C1 = case when :new.is_root = 1 then 'Yes'
                  else 'No' end
        , C2 = :new.col2
        , C3 = :new.col3
        , C4 = :new.col4
        ....
        , C40 = :new.col40
    where id = :new.id;
end;
/

Any column not included in the table update statement cannot be updated through the view.

Answer 2

I believe what you're attempting is impossible. Whilst MySQL does allow update statements to be performed against views, is_root is a derived column, so updating it makes no sense (as there's no path back to the table.