w3resource

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 table: agentsview
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

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:

Sql update views

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
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

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:

Sql update views

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
   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

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:

Sql updatable views using in operator

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
   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

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
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

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
   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012
Sample table: agents
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql/update-views/sql-update-views.php