SQL update views

Update View

The SQL UPDATE VIEW command can be used to modify the data of 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.

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<column1>=<value1>,<column2>=<value2>,.....
WHERE <condition>;

Parameters:

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  daywiseorder SET ord_count=2
WHERE ord_count=1;

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:

SELECT * FROM daywiseorder;

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:

CREATE VIEW myclient(client_name,client_no,outspercent)
AS SELECT    cust_name,cust_code
outstanding_amt*100/(opening_amt+receive_amt)
FROM customer
WHERE cust_country='USA'
AND  outstanding_amt*100/(opening_amt+receive_amt)>50;

To update the view 'myclient' with following condition -

1. 'outspercent' set at 80,

the following SQL statement can be used:

SQL Code:

UPDATE myclient SET outspercent=80;

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:

SELECT * FROM myclient;

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:

CREATE VIEW myagent>
AS SELECT *
FROM agents
WHERE agent_code IN
(SELECT agent_code
FROM orders
WHERE ord_date='15-AUG-08');

To update the view 'myagent' with following condition -

1. 'commission' set at .15,

the following SQL statement can be used:

SQL Code:

UPDATE myagent SET commission=.15;

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

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

Previous: Create view with join
Next: Create Index