Altering an Existing View
1. Changing View Columns
If a view is created such that it includes all (or certain) columns of a table. we can change the columns used in the view using the ALTER VIEW statement.
Syntax:
ALTER VIEW view_name column_list AS select_statement;
Once the execution of the ALTER VIEW statement becomes successful, MySQL will update a view and stores it in the database. We can see the altered view using the SELECT statement, as shown in the output:
Query:
ALTER VIEW myView (id,first_name,city)
AS
SELECT id, upper(first_name), city
FROM employee;
//verify the view
SELECT * from myView;
Output:
2. Adding One More Column
Suppose we have to add the column to the table and then modify the view to show it as well. So, we can use the ALTER statement as well where create is and add our new columns; don’t forget to include the old ones as well because instead of what a typical alter statement does, here, we have to recreate the existing view with the new columns .
Syntax:
ALTER VIEW view_name column_list, new_column_to_add AS select_statement;
Let’s see this using an example:
Query:
ALTER VIEW myView (id,first_name,city,emp_details)
AS
SELECT id, upper(first_name), city, description
FROM
employee;
Output:
3. Removing a Column
We can’t remove a column from a view. We must instead alter the definition of the view. All tables in the INFORMATION_SCHEMA database are actually views to ease access to the information they contain. It is not possible to alter them. We can use ALTER VIEW statement drop a column in a view.
We already have the a view called “myView” with 4 columns, if we want to remove column “city“, we can just alter the view like:
Query:
ALTER VIEW myView (id,first_name,emp_details)
AS
SELECT id, upper(first_name), description
FROM
employee;
Output:
4. Changing Data in a Column
We can also update the data of an MySQL view using UPDATE statement. This will not update the view’s MySQL query but actual table data. UPDATE statement works on MySQL views only if they are direct subset of table data, without any aggregation or modification. So we can use UPDATE statement on views only when:
- It doesn’t have DISTINCT, GROUP BY, HAVING, Aggregations, SET functions or operators.
- Doesn’t refer to multiple tables.
- Doesn’t have calculated columns.
Query:
UPDATE myView set emp_details = "GFG"
WHERE id = 6;
Output:
MySQL – ALTER VIEW Statement
The ALTER VIEW statement in MySQL is a powerful tool that allows users to modify the definition of an existing view without the need to drop and recreate it. This statement is particularly useful for changing the query or structure of a view to better help the needs of the application or database design.
In this article, We will learn about What is ALTER VIEW Statement, How to perform the ALTER VIEW Statement in multiple situations with examples, and so on.