How to Update SQL Views - dummies

By Allen G. Taylor

After you create a SQL table, that table is automatically capable of accommodating insertions, updates, and deletions. Views don’t necessarily exhibit the same capability. If you update a view, you’re actually updating its underlying table. Here are a few potential problems you may encounter when you update views:

  • Some views may draw components from two or more tables. If you update such a view, the underlying tables may not be updated properly.

  • A view may include an expression in a SELECT list. Because expressions don’t map directly to rows in tables, your DBMS won’t know how to update an expression.

Suppose you create a view by using the following statement:

 AS SELECT EmpName, Salary+Comm AS Pay

You may think you can update Pay by using the following statement:

UPDATE COMP SET Pay = Pay + 100 ;

Unfortunately, this approach doesn’t make any sense. That’s because the underlying table has no Pay column. You can’t update something that doesn’t exist in the base table.

Keep the following rule in mind whenever you consider updating views: You can’t update a column in a view unless it corresponds to a column in an underlying base table.