w3resource logo


>Sql update views

SQL update views

Secondary Nav

Description

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.

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

Parameters

Name Description
view_name Name of the virtual table or view where data will be modified.
column1,column2 Name of the columns of the table.
value1,value2 Values for the columns which are going to be updated.
condition Condition 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 :

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

Output

Sql update views

To execute query on this view

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' :

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 :

UPDATE countryagent
SET commission=.13;  

Output

Sql update views

To execute query on this view

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' :

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 :

UPDATE orderindate SET advance_amount=2000
WHERE advance_amount=1500;

Output

Sql updatable views using in operator

To execute query on this view

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' :

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 :

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

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' :

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 :

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

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' :

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 :

UPDATE myagent SET commission=.15;

See our Model Database



Join our Question Answer community to learn and share your programming knowledge.