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:
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:
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:
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.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Create view with join
Next: Create Index