Multi-table views

This restriction determines what types of modifications you can make through views that join columns from multiple tables. Adaptive Server prohibits delete statements on multi-table views, but allows update and insert statements that would not be allowed in other systems.

You can insert or update a multi-table view if:

The view has no with check option clause.
All columns being inserted or updated belong to the same base table.

For example, consider the following view, which includes columns from both titles and publishers and has no with check option clause:

create view multitable_view
as select title, type, titles.pub_id, state
from titles, publishers
where titles.pub_id = publishers.pub_id

A single insert or update statement can specify values either for the columns from titles or for the column from publishers:

update multitable_view
set type = "user_friendly"
where type = "popular_comp"

However, this statement fails because it affects columns from both titles and publishers:

update multitable_view
set type = "cooking_trad",
state = "WA"
where type = "trad_cook"