About the WHERE Clause of an Updatable View

In a CREATE VIEW statement, for a view to be updatable, the WHERE clause has the following characteristics:

  • The LIMIT keyword is allowed.

  • The IN keyword with either a list or a subquery to provide the IN list values is allowed.

  • Allows update or delete of only those rows that meet the view’s WHERE clause criteria. In other words, you cannot update or delete a row that would not otherwise be selected from the view.

  • WHERE clause that contains an expression must have an expression that is one of the following:

    • Comparison Operators: <, <=,>,>=

    • Logical Operators: AND, OR, NOT

    • Math Operators: +, -, *, /, Remainder (%)

    • Other Operators: Concatenate (||), Unary Minus Operator, IS NULL, IS NOT NULL

    • Table column name

    • Constants: string constants, integers, true, false