MySQL ALTER View

In this tutorial, we will learn how to alter an existing view in MySQL using the ALTER VIEW statement. This is a super simple process, therefore it will take very little time. Also, we will see how can we alter a view using two methods- using a MySQL CLI and using a MySQL workbench. So, let’s get started!

Also read: How to Alter a Procedure in MySQL

Introduction to MySQL ALTER VIEW

The definition of a view, which must exist, is altered by this statement. It has a syntax similar to that of CREATE VIEW. The privileges CREATE VIEW and DROP for the view, as well as some privileges for each column mentioned in the SELECT query, are needed to execute this operation.

Only the definer or users with the SET USER ID capability are allowed to ALTER VIEW.

Syntax of MySQL ALTER VIEW

As said earlier, the syntax of the ALTER VIEW is very much similar to the CREATE VIEW statement.

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]Code language: SQL (Structured Query Language) (sql)

For detailed information about the options in the given syntax, you can visit the official documentation or the brief tutorial on the CREATE VIEW statement.

Example of MySQL ALTER VIEW

Before demonstrating the view-altering procedure, we will need a table and a view. So, let’s create a table first and then we will create a view.

-- creating the table
create table emps( 
id int primary key auto_increment, 
name varchar(100), 
age int);

-- inserting the data
insert into emps(name,age) values
('Bob',19),('Mark',21),('Albert',20),('May',21);

-- creating a view
CREATE VIEW getEmps AS 
SELECT * FROM emps;
Code language: SQL (Structured Query Language) (sql)

Let’s test the view now to check if it is working correctly.

SELECT * FROM getEmps;Code language: SQL (Structured Query Language) (sql)

Perfect!

Alter View using MySQL CLI

We will now see how to alter a view using the MySQL CLI.

ALTER VIEW getEmps AS
SELECT name,age FROM emps;Code language: SQL (Structured Query Language) (sql)

The above query will alter the existing definition of the view.

Let’s now display the data of the altered view.

SELECT * FROM getEmps;Code language: SQL (Structured Query Language) (sql)

We have updated the view definition to display only names and the age of employees. And as you can see, we got the correct result, which means the view has been altered successfully.

Alter View using MySQL Workbench

We can also modify the view definition using the MySQL workbench. It is a very easy method as you don’t need to write a lengthy query. You just need to modify the existing query displayed in the editor.

Follow the steps given below –

• Open the MySQL workbench. On the left-hand side, you can see the database names present on your server.
• Click on the database and you will see the VIEW options below the TABLES option. Click on the VIEWS and you will see all the views that are associated with that database.

• Right-click on the view that you want to alter.
• Modify the view definition.
• In our case, we will set the view definition to fetch all the table data as it was originally created.

• Click on the Apply button. On the next screen, you will be asked for confirmation. Click on the apply button to save the changes.
• That’s it. Now test the view to check if the changes took place.

As you can see, the changes have taken place successfully.

Summary

In this tutorial, we learned to modify the view definition using the MySQL CLI as well as MySQL workbench. It is a pretty simple procedure and hardly takes five to ten minutes to perform. You can try changing the algorithm of the views also.

References

MySQL official documentation on altering the view.