Category: Views

There are various SQL statements that perform different operations on database objects, such as creating, updating, deleting and also renaming a database object. And since a view is also a database object, all these operations can also be performed on a view, you can create a view, update a view, delete a view and also rename a view.

There is no direct query to rename a view in SQL. In MySQL we can rename a view using the RENAME TABLE statement and in MS SQL Server we can rename a view using the sp_rename procedure.

In many cases, deleting the existing view and then re-creating it with a new name is rather recommended.

Renaming a View in MySQL

The RENAME TABLE statement in MySQL database is used to rename views. You just have to make sure that the new name of the view does not overlap with the name of any existing views.

Syntax

Following is the basic syntax to rename a view in MySQL −

RENAMETABLE old_view_name To new_view_name;

Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Also before executing this statement, it is important to make sure that the table is not locked and there are no active transactions.

Example

In this example, let us first create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Now insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Following query creates a view based on the above created table −

CREATEVIEW CUSTOMERS_VIEW ASSELECT*FROM CUSTOMERS WHERE AGE >25;

You can verify the contents of a view using the select query as shown below −

SELECT*from CUSTOMERS_VIEW;

The view is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

Now we know that a view with the name CUSTOMERS_VIEW exists in our database. So, we are directly going to rename this view to VIEW_CUSTOMERS, using the following query −

RENAMETABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;

Output

The result obtained is as shown below −

Query OK, 0 rows affected (0.08 sec)

Verification

We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view −

SELECT*from VIEW_CUSTOMERS;

The view displayed is as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

Renaming a View in SQL Server

There isn’t a query in SQL Server that can rename a view directly. But, it does give you access to a stored procedure called sp_rename that can rename a view. You have to make sure there are no active transactions being performed on the view using its old name before renaming it.

The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints.

Syntax

Following is the basic syntax to rename a view in SQL −

EXEC sp_rename 'old_view_name','new_view_name'

Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Before executing this statement, it is important to make sure that the table is not locked and there are no active transactions.

Example

In this example, let us first try to create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Now insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00);

Following query creates a view based on the above created table −

CREATEVIEW CUSTOMERS_VIEW ASSELECT*FROM CUSTOMERS WHERE SALARY >2000;

You can verify the contents of a view using the select query as shown below −

SELECT*from CUSTOMERS_VIEW;

The view will be created as −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00

Now, we know that we have an existing view CUSTOMERS_VIEW in our database. So, we are going to rename this view to VIEW_CUSTOMERS, using the following query −

EXEC sp_rename CUSTOMERS_VIEW, VIEW_CUSTOMERS;

Verification

We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view −

SELECT*FROM VIEW_CUSTOMERS;

The view displayed is as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00

We have renamed the view to VIEW_CUSTOMERS; if the user tries to get the details by using the old view name, it will throw an error showing that the view does not exist.

Rules to be followed while Renaming Views

When renaming views in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues.

Here are some general rules to keep in mind when renaming views in SQL −

• Avoid renaming system views − System views are views that contain all the information about the database management system. Renaming these views can cause issues with the functioning of the database system, so it is generally not recommended to rename system views.
• Update all references to the view − After renaming a view, any stored procedures, triggers, or other database objects that reference the view will need to be updated to use the new name of the view. Failure to update these references can result in errors or issues with the functioning of the database system.
• Test thoroughly − Before renaming a view in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the view have been updated correctly and that the database system continues to function as expected.
• Use a consistent naming convention − It is a good practice to use a consistent naming convention for views and other database objects to make it easier to understand and maintain the database system. If you need to rename a view, consider following the same naming convention that you have used for other views in the database.
• Backup the database − Before renaming a view, it is recommended to create a backup of the database to ensure that you have a restore point; in case anything goes wrong during the renaming process.

SQL allows you to drop an exiting view and delete records from a view in a database. SQL uses DROP statement to delete all the records from the view along with its definition and using the DELETE statement, only the records are deleted while the view definition of the view remains unchanged.

And note that if a record is deleted from a view, it is also deleted from its corresponding base table.

The DROP VIEW Statement

The SQL DROP VIEW statement is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also drop indexed views with this statement.

Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped explicitly using the DROP VIEW command.

• While trying to perform queries, the database engine checks all the objects referenced in that statement are valid and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error.
• To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.

Syntax

The basic syntax of this DROP VIEW statement is as follows −

DROPVIEW view_name;

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

CREATETABLE CUSTOMERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

Now, insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

Assume we have created 3 views using the CREATE VIEW statement as shown below −

CREATEVIEW CUSTOMERS_VIEW1 ASSELECT*FROM CUSTOMERS;CREATEVIEW CUSTOMERS_VIEW2 ASSELECT*FROM CUSTOMERS;CREATEVIEW CUSTOMERS_VIEW3 ASSELECT*FROM CUSTOMERS;

You can verify the list of all the views using the following query −

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='tutorials';

This will display the list of views as follows −

TABLE_SCHEMA	TABLE_NAME
tutorials	CUSTOMERS_VIEW1
tutorials	CUSTOMERS_VIEW2
tutorials	CUSTOMERS_VIEW3

Now, lets drop two views from the above created views using the DROP VIEW statement.

DROPVIEW CUSTOMERS_VIEW1;DROPVIEW CUSTOMERS_VIEW2;

Verification

Once we have deleted all the views if you try to retrieve the list of views you will get an empty set as shown below −

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='tutorials';

The remaining list of views is as follows −

TABLE_SCHEMA	TABLE_NAME
tutorials	CUSTOMERS_VIEW3

The IF EXISTS clause

While deleting an existing view, you can use the IF EXISTS clause in the DROP VIEW statement. This clause, when specified in the DROP VIEW query, will automatically check whether the view exists in the current database and then drops it, if yes. If the view does not exist in the database, the query will be ignored.

Syntax

Following is the basic syntax of DROP VIEW IF EXISTS −

DROPVIEW[IFEXISTS] view_name;

Example

If you try to drop a view that doesn’t exist in the database, without using the IF EXISTS clause, as shown below −

DROPVIEW DEMO_VIEW;

An error will be generated −

ERROR 1051 (42S02): Unknown table 'tutorials.demo_view'

But if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the specified event will be dropped; and if a view with the given name doesn’t exist the query will be ignored.

DROPVIEWIFEXISTS DEMO_VIEW;

The query will be ignored with the following output displayed −

Query OK, 0 rows affected, 1 warning (0.04 sec)

Deleting Rows from a View

Instead of removing an entire view, we can also delete selected rows of a view using the DELETE statement.

Syntax

Following is the syntax of the DELETE statement −

DELETEFROM view_name WHERE condition;

Example

Following query deletes a record from the third_view created on the CUSTOMERS table created above. The changes made to the data in view will finally be reflected in the base table CUSTOMERS.

DELETEFROM CUSTOMERS_VIEW3 WHERE AGE =22;

This would ultimately delete a row from the base table CUSTOMERS and the same would reflect in the view itself. Now, query the base table and the SELECT statement would produce the following result −

SELECT*FROM CUSTOMERS;

The CUSTOMERS table is displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	35	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

SQL UPDATE View Statement

A view is a database object that can contain rows (all or selected) from an existing table. It can be created from one or many tables which depends on the provided SQL query to create a view.

Unlike CREATE VIEW and DROP VIEW there is no direct statement to update the records of an existing view. We can use the SQL UPDATE Statement to modify the existing records in a table or a view.

Syntax

The basic syntax of the UPDATE query with a WHERE clause is as follows −

UPDATE view_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE[condition];

You can combine N number of conditions using the AND or the OR operators.

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

CREATETABLE CUSTOMERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

Now, insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

Following query creates a view based on the above created table −

CREATEVIEW CUSTOMERS_VIEW ASSELECT*FROM CUSTOMERS;

You can verify the contents of a view using the SELECT query as shown below −

SELECT*FROM CUSTOMERS_VIEW;

The view will be displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Following query updates the age of Ramesh to 35 in the above created CUSTOMERS_VIEW −

UPDATE CUSTOMERS_VIEW 
SET AGE =35WHERE name ='Ramesh';

Verification

You can verify the contents of the CUSTOMERS_VIEW using the SELECT statement as follows −

SELECT*FROM CUSTOMERS_VIEW WHERE NAME ='Ramesh';

The resultant view would have the following record(s) −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	35	Ahmedabad	2000.00

Example

The following query will update the ADDRESS of a customer whose ID is 6 in the CUSTOMERS_VIEW.

UPDATE CUSTOMERS_VIEW SET ADDRESS ='Pune'WHERE ID =6;

Output

The query produces the following output −

Query OK, 1 row affected (0.21 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

If you retrieve the record with ID value 6 using the SELECT statement as −

SELECT*FROM CUSTOMERS_VIEW WHERE ID=6;

The record returned would be −

ID	NAME	AGE	ADDRESS	SALARY
6	Komal	22	Hyderabad	4500.00

Updating Multiple Rows and Columns

Using UPDATE statement, multiple rows and columns in a view/table can also be updated. While updating multiple rows, specify the condition in a WHERE clause such that only required rows would satisfy it.

Example

Following query updates the NAME and AGE column values in the CUSTOMERS_VIEW of the record with ID value 3.

UPDATE CUSTOMERS_VIEW
SET NAME ='Kaushik Ramanujan', AGE =24WHERE ID =3;

Output

The query produces the following output −

Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

You can verify whether the record is updated or not, using the following query −

SELECT*FROM CUSTOMERS_VIEW WHERE ID =3;

The record returned would be −

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik Ramanujan	24	Kota	2000.00

Example

But if you want to modify/update the age values of all the records in the CUSTOMERS_VIEW, there is no need to use the WHERE clause.

UPDATE CUSTOMERS_VIEW SET AGE = AGE+6;

Output

This query produces the following output −

Query OK, 7 rows affected (0.10 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

To verify whether the records of the CUSTOMERS_VIEW are modified or not, use the following SELECT query −

SELECT*FROM CUSTOMERS_VIEW;

The resultant CUSTOMERS_VIEW would have the following records −

ID	NAME	AGE
1	Ramesh	41
2	Khilan	31
3	Kaushik Ramanujan	30
4	Chaitali	31
5	Hardik	33
6	Komal	28
7	Muffy	30

What is SQL View

A view in SQL is a virtual table that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query. A view can contain rows from an existing table (all or selected). A view can be created from one or many tables. Unless indexed, a view does not exist in a database.

The data in the view does not exist in the database physically. A view is typically created by the database administrator and is used to −

• Structure data in a way that users or classes of users find natural or intuitive.
• Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
• Summarize data from various tables which can be used to generate reports.

The SQL CREATE VIEW Statement

To create a view in a database, you can use the SQL CREATE VIEW statement.

Syntax

Following is the syntax of the SQL CREATE VIEW statement −

CREATEVIEW view_name ASSELECT column1, column2....FROM table_name
WHERE[condition];

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

CREATETABLE CUSTOMERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

Now, insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

Following query creates a view based on the above created table −

CREATEVIEW CUSTOMERS_VIEW ASSELECT*FROM CUSTOMERS;

Verification

You can verify the contents of a view using the select query as shown below −

SELECT*FROM CUSTOMERS_VIEW;

The view is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Create View With WHERE Clause

We can also create a view with only specific records from a table using the where clause along with the SQL CREATE VIEW statement as shown below −

CREATEVIEW BUYERS_VIEW asSELECT*FROM CUSTOMERS 
WHERE SALARY >3000;

Verification

Following are the contents of the above created view −

SELECT*FROM BUYERS_VIEW;

The view is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

The WITH CHECK OPTION Clause

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) specified by the WHERE clause.

If they do not satisfy the condition(s), the UPDATE or INSERT statements return an error. The following example creates the view named BUYERS_VIEW with the WITH CHECK OPTION clause.

CREATEVIEW MY_VIEW ASSELECT name, age
FROM  CUSTOMERS
WHERE age >=25WITHCHECKOPTION;

The WITH CHECK OPTION in this case should deny the entry and updates of the of records whose age value is greater than or equal to 25.

Verification

Following are the contents of the above created view −

SELECT*FROM MY_VIEW;

The view is displayed as follows −

NAME	AGE
Ramesh	32
Khilan	25
Chaitali	25
Hardik	27