In this topic, we described about the Alter View with detailed example.
In some cases, we my required to change the structure of the view due to either adding additional columns or hiding the unwanted columns from displaying to the users. For this purpose, we can use the ALTER VIEW. In ALTER VIEW statement, we can add new columns, or we can remove the columns without deleting the view.
By using ALTER VIEW statement, we can change the Structure of the view.
ALTER VIEW existing_view_name AS SELECT column1, column2, …, columnN FROM table_name [WHERE condition];
- existing_view_name - Specifies the name of the existing view.
- column1, column2, …, columnN - Specifies the column names.
- table_name - Specifies the base table name.
- condition - Specifies the condition name.
Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.
Scenario – Change the existing view without adding new columns.
Requirement – Change the existing view employee_v by deleting designation column form it. The query was as follows -
ALTER VIEW employee_v AS SELECT emp_id, emp_name FROM employee_details;
By executing above query, we can change the employee_v view. The output was shown as below -
Scenario – Change the existing view with adding new columns.
Requirement – Change the existing view employee_v by dding a column manager_id column. The query was as follows -
ALTER VIEW employee_v AS SELECT emp_id, emp_name, manager_id FROM employee;
By executing above query, we can change the employee_v view by adding new column without deleting the existing view. The output was shown as below –