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:

-- Updating the agentview view
UPDATE agentview
-- Setting the commission to 0.13 for agents working in London
SET commission = 0.13
-- Specifying the condition for the update operation
WHERE working_area = 'London';

Explanation:

• This SQL code updates the "agentview" view.


• The UPDATE statement is used to modify existing records in a table or view.


• In this case, the view "agentview" is being updated.


• The SET clause specifies the column to be updated, in this case, the commission column.


• The new value for the commission column is set to 0.13.


• The WHERE clause is used to specify the condition for the update operation.


• Only rows where the working_area is 'London' will have their commission updated to 0.13.


• Once executed, this SQL statement will update the commission of agents working in London to 0.13 in the "agentview" view.

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:

-- Creating a view named countryagent
CREATE VIEW countryagent
-- Defining the view's query to select all columns
-- from the agents table for agents working in Brisbane
AS SELECT *
-- Selecting all columns from the agents table
FROM agents
-- Filtering rows to retain only those where the working_area is 'Brisbane'
WHERE working_area = 'Brisbane';

Explanation:

• This SQL code creates a view named "countryagent".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "countryagent" is defined by selecting all columns (*) from the "agents" table.


• The WHERE clause is used to filter the rows, retaining only those where the working_area is 'Brisbane'.


• Once created, the view "countryagent" will contain all columns from the "agents" table for agents working in Brisbane. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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

SQL Code:

-- Updating the countryagent view
UPDATE countryagent
-- Setting the commission to 0.13 for all agents in the countryagent view
SET commission = 0.13;

Explanation:

• This SQL code updates the "countryagent" view.


• The UPDATE statement is used to modify existing records in a table or view.


• In this case, the view "countryagent" is being updated.


• The SET clause specifies the column to be updated, in this case, the commission column.


• The new value for the commission column is set to 0.13.


• Since no WHERE clause is provided, the update operation will apply to all rows in the "countryagent" view.


• Once executed, this SQL statement will update the commission of all agents in the "countryagent" view to 0.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:

-- Creating a view named orderindate
CREATE VIEW orderindate
-- Defining the view's query to select specific columns
-- from the orders table for orders placed on specific dates
AS SELECT agent_code, ord_num, cust_code, advance_amount
-- Selecting agent_code, ord_num, cust_code, and advance_amount columns
FROM orders
-- Filtering rows to retain only those where the ord_date is either '15-APR-08' or '15-Aug-08'
WHERE ord_date IN ('15-APR-08', '15-Aug-08');

Explanation:

• This SQL code creates a view named "orderindate".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "orderindate" is defined by selecting specific columns (agent_code, ord_num, cust_code, and advance_amount) from the "orders" table.


• The WHERE clause is used to filter the rows, retaining only those where the ord_date is either '15-APR-08' or '15-Aug-08'.


• Once created, the view "orderindate" will contain information about orders placed on the specified dates. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

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:

-- Updating the orderindate view
UPDATE orderindate
-- Setting the advance_amount to 2000 for rows where advance_amount is currently 1500
SET advance_amount = 2000
-- Specifying the condition for the update operation
WHERE advance_amount = 1500;

Explanation:

• This SQL code updates the "orderindate" view.


• The UPDATE statement is used to modify existing records in a table or view.


• In this case, the view "orderindate" is being updated.


• The SET clause specifies the column to be updated, in this case, the advance_amount column.


• The new value for the advance_amount column is set to 2000.


• The WHERE clause is used to specify the condition for the update operation.


• Only rows where the advance_amount is currently 1500 will have their advance_amount updated to 2000.


• Once executed, this SQL statement will update the advance_amount of rows in the "orderindate" view where it is currently 1500 to 2000.

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:

-- Creating a view named daywiseorder
CREATE VIEW daywiseorder(ord_date, ord_count)
-- Defining the view's query to select the order date and the count of orders
-- from the orders table grouped by ord_date
AS SELECT ord_date, COUNT(*)
-- Selecting the ord_date column and counting occurrences from the orders table
FROM orders
-- Grouping the rows by ord_date
GROUP BY ord_date;

Explanation:

• This SQL code creates a view named "daywiseorder".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view with two columns: ord_date and ord_count.


• The view's query selects the ord_date column and counts the occurrences of each ord_date from the "orders" table.


• The rows are grouped by the ord_date column using the GROUP BY clause.


• Once created, the view "daywiseorder" will contain the order dates and the count of orders for each date in the "orders" table. This view can be queried like a regular table, providing a convenient way to access this aggregated subset of data.

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:

-- Updating the daywiseorder view
UPDATE daywiseorder
-- Setting the ord_count to 2 for rows where ord_count is currently 1
SET ord_count = 2
-- Specifying the condition for the update operation
WHERE ord_count = 1;

Explanation:

• This SQL code updates the "daywiseorder" view.


• The UPDATE statement is used to modify existing records in a table or view.


• In this case, the view "daywiseorder" is being updated.


• The SET clause specifies the column to be updated, in this case, the ord_count column.


• The new value for the ord_count column is set to 2.


• The WHERE clause is used to specify the condition for the update operation.


• Only rows where the ord_count is currently 1 will have their ord_count updated to 2.


• Once executed, this SQL statement will update the ord_count of rows in the "daywiseorder" view where it is currently 1 to 2.

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:

-- Creating a view named myclient
CREATE VIEW myclient(client_name, client_no, outspercent)
-- Defining the view's query to select specific columns and calculate outspercent
-- from the customer table for clients meeting certain conditions
AS SELECT cust_name, cust_code,
   -- Calculation for outspercent: (outstanding_amt * 100) / (opening_amt + receive_amt)
   outstanding_amt * 100 / (opening_amt + receive_amt)
-- Selecting cust_name and cust_code columns from the customer table
FROM customer
-- Filtering rows to retain only those meeting specific conditions
WHERE cust_country = 'USA'
AND outstanding_amt * 100 / (opening_amt + receive_amt) > 50;

Explanation:

• This SQL code creates a view named "myclient".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view with three columns: client_name, client_no, and outspercent.


• The view's query selects the cust_name and cust_code columns from the customer table.


• The outspercent column is calculated as (outstanding_amt * 100) / (opening_amt + receive_amt), representing the percentage of outstanding amount relative to the sum of opening amount and receive amount for each client.


• The WHERE clause is used to filter the rows, retaining only those where cust_country is 'USA' and outspercent is greater than 50.


• Once created, the view "myclient" will contain information about clients meeting the specified conditions. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

To update the view 'myclient' with following condition -

1. 'outspercent' set at 80,

the following SQL statement can be used:

SQL Code:

-- Updating the myclient view
UPDATE myclient
-- Setting the outspercent to 80 for all rows in the myclient view
SET outspercent = 80;

Explanation:

• This SQL code updates the "myclient" view.


• The UPDATE statement is used to modify existing records in a table or view.


• In this case, the view "myclient" is being updated.


• The SET clause specifies the column to be updated, in this case, the outspercent column.


• The new value for the outspercent column is set to 80.


• Since no WHERE clause is provided, the update operation will apply to all rows in the "myclient" view.


• Once executed, this SQL statement will update the outspercent column of all rows in the "myclient" view to 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:

-- Creating a view named myagent
CREATE VIEW myagent
-- Defining the view's query to select all columns
-- from the agents table for agents associated with orders placed on a specific date
AS SELECT *
-- Selecting all columns from the agents table
FROM agents
-- Filtering rows to retain only those where the agent_code is in
-- the set of agent codes associated with orders placed on '15-AUG-08'
WHERE agent_code IN
    (SELECT agent_code
     -- Subquery to select agent codes from the orders table
     FROM orders
     -- Filtering orders to those placed on '15-AUG-08'
     WHERE ord_date = '15-AUG-08');

Explanation:

• This SQL code creates a view named "myagent".


• Views are virtual tables that represent the result of a stored query.


• The CREATE VIEW statement is used to define a new view.


• In this case, the view "myagent" is defined by selecting all columns (*) from the "agents" table.


• The WHERE clause filters the rows, retaining only those where the agent_code is in the set of agent codes associated with orders placed on '15-AUG-08'.


• The subquery inside the WHERE clause selects agent codes from the orders table, filtering orders to those placed on '15-AUG-08'.


• Once created, the view "myagent" will contain information about agents associated with orders placed on '15-AUG-08'. This view can be queried like a regular table, providing a convenient way to access this filtered subset of data.

To update the view 'myagent' with following condition -

1. 'commission' set at .15,

the following SQL statement can be used:

SQL Code:

-- Updating the myagent view
UPDATE myagent
-- Setting the commission to 0.15 for all rows in the myagent view
SET commission = 0.15;

Explanation:

• This SQL code updates the "myagent" view.


• The UPDATE statement is used to modify existing records in a table or view.


• In this case, the view "myagent" is being updated.


• The SET clause specifies the column to be updated, in this case, the commission column.


• The new value for the commission column is set to 0.15.


• Since no WHERE clause is provided, the update operation will apply to all rows in the "myagent" view.


• Once executed, this SQL statement will update the commission column of all rows in the "myagent" view to 0.15.

See our Model Database

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

Previous: Create view with join
Next: Create Index