Which of the following restrictions must be applied while creating a view?

All views are not updatable. So, UPDATE command is not applicable to all views. An updatable view is one which allows performing a UPDATE command on itself without affecting any other table.

Contents:

When can a view be updated?

1. The view is defined based on one and only one table.

2. The view must include the PRIMARY KEY of the table based upon which the view has been created.

3. The view should not have any field made out of aggregate functions.

4. The view must not have any DISTINCT clause in its definition.

5. The view must not have any GROUP BY or HAVING clause in its definition.

6. The view must not have any SUBQUERIES in its definitions.

7. If the view you want to update is based upon another view, the later should be updatable.

8. Any of the selected output fields (of the view) must not use constants, strings or value expressions.

Syntax:

UPDATE < view_name > SET=,=,.....
WHERE ;

Parameters:

NameDescriptionview_nameName of the virtual table or view where data will be modified.column1,column2Name of the columns of the table.value1,value2Values for the columns which are going to be updated.conditionCondition or criteria.

Example:

Sample view: agentview

To update the view 'agentview' with following conditions -

1. 'commission' must be set at .13,

2. 'working_area' must be 'London',

the following SQL statement can be used:

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

Output:

To execute query on this view

SQL Code:

SELECT * FROM agentview;

SQL updatable views

In the following topic, we are discussing, how a view can be updated in a UPDATE VIEW statement.

Example:

Sample table: agents

This statement bellow creates a updatable view 'countryagent':

SQL Code:

CREATE VIEW countryagent
AS SELECT *
FROM agents
WHERE  working_area='Brisban';

To update the column 'commission' with the value .13 of the view 'countryagent', the following SQL statement can be used:

SQL Code:

UPDATE countryagent
SET commission=.13;  

Output:

To execute query on this view

SQL Code:

SELECT * FROM countryagent;

SQL updatable views using in operator

In the following topic we are going to discuss, how SQL IN operator can be used in a UPDATE VIEW statement to update the data of columns in a view.

Example:

Sample table: orders

This statement bellow creates a updatable view 'orderindate':

SQL Code:

CREATE VIEW orderindate
AS SELECT agent_code,ord_num,cust_code,advance_amount
FROM orders
WHERE ord_date IN ('15-APR-08','15-Aug-08');

To update the view 'orderindate' with following conditions -

1. 'advance_amount' set at 2000,

2. 'advance_amount' must be 1500,

the following SQL statement can be used:

SQL Code:

UPDATE orderindate SET advance_amount=2000
WHERE advance_amount=1500;

Output:

To execute query on this view

SQL Code:

SELECT * FROM orderindate;

SQL updatable views with aggregate function

Here in the following topics, we are discussing, that a view can not be updated (using a UPDATE VIEW statement) if any of the fields of the view is created by using either an AGGREGATE FUNCTION or a GROUP BY clause.

Example:

Sample table: orders

This statement bellow creates a view 'daywiseorder':

SQL Code:

CREATE VIEW daywiseorder(ord_date,ord_count)
AS SELECT ord_date,COUNT(*)
FROM orders
GROUP BY ord_date;

To update the view 'daywiseorder' with following conditions -

1. 'ord_count' set at 2,

2. 'ord_count' must be 1,

the following SQL statement can be used :

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

Note:

This view is a not an updatable view. The aggregate function 'COUNT' have been used in the definition of the view so this view is not updatable i.e. the 'view' is read only.

To execute query on this view

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

SQL update views with arithmetic expression

In the following topics, we are discussing, that a view can not be updated (using a UPDATE VIEW statement) if any of the fields of the view is created by using an arithmetic expression.

Example:

Sample table: customer

This statement bellow creates a view 'myclient':

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

To update the view 'myclient' with following condition -

1. 'outspercent' set at 80,

the following SQL statement can be used:

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

Note:

This view is a not an updatable view. Arithmetic expression has been used in the definition of the view. So this view is not updatable i.e. the 'view' is read only.

To execute query on this view

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

SQL update views using subqueries

In this page, we are discussing, that a view can not be updated (using a UPDATE VIEW statement) if any of the fields of the view is created by using a subquery.

Example:

Sample table: orders

Sample table: agents

This statement bellow creates a view 'myagent':

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

To update the view 'myagent' with following condition -

1. 'commission' set at .15,

the following SQL statement can be used:

SQL Code:

UPDATE agentview
SET commission=.13
WHERE working_area=’London’;

See our Model Database

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Create view with join
Next: Create Index

SQL: Tips of the Day

Equals(=) vs. LIKE

LIKE and = are different operators. Most answers here focus on the wildcard support, which is not the only difference between these operators!

= is a comparison operator that operates on numbers and strings. When comparing strings, the comparison operator compares whole strings.

LIKE is a string operator that compares character by character.

To complicate matters, both operators use a collation which can have important effects on the result of the comparison.

What are some of the limitations of view?

What are the restrictions that are put on update operations using a view?

Which of the following command is used to create view?

Which type of clause must be included in the CREATE VIEW statement?