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.
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:
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;
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Create view with join
Next: Create Index
SQL: Tips of the Day
How to combine date from one field with time from another field - MS SQL Server
You can simply add the two.
- if the Time part of your Date column is always zero
- and the Date part of your Time column is also always zero (base date: January 1, 1900)
Adding them returns the correct result-
SELECT Combined = MyDate + MyTime FROM MyTable
Ref: https://bit.ly/3wldJYf
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook