ALTER VIEW

Use this MySQL statement to change a view, which are created with CREATE VIEW, of course.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER 
  [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}] 
  [DEFINER =  = {'user'@'host'|CURRENT_USER}]
  [SQL SECURITY {DEFINER|INVOKER }]
VIEW view [(column, ...)] 
AS SELECT...
[WITH [CASCADED|LOCAL] CHECK OPTION]

Explanation

Use this MySQL statement to change a view. Views are available as of version 5.0.1 of MySQL.

This MySQL statement is used primarily to change the MySQL statement, SELECT that determines the view, which you can do by simply placing the new SELECT statement for the view after the AS keyword.

Change the column names provided by the view to queries by providing the new column names in a comma-separated list within the parentheses following the view's name. Don't include either the old SELECT statement or the old column names in the statement.

The ALGORITHM parameter changes algorithmic method to use for processing a view: the choices are MERGE or TEMPTABLE. TEMPTABLE prevents a view from being updatable.

The DEFINER clause can change the user account considered to be the view's creator. This clause is available as of version 5.1.2 of MySQL. The same version introduced the related SQL SECURITY clause. It instructs MySQL to authorize access to the view based on the privileges of either the user account of the view's creator (DEFINER) or the user account of the user who is querying the view (INVOKER). This can help to prevent some users from accessing restricted views.

The WITH CHECK OPTION clause can change the restrictions on the updating of a view to only rows in which the WHERE clause of the underlying SELECT statement returns true. For a view that is based on another view, if you include the LOCAL keyword, this restriction will be limited to the view in which it's given and not the underlying view. If you specify CASCADED instead, underlying views will be considered as well.

Examples

An example follows of this MySQL statement's use:

ALTER VIEW student_directory(ID, Name, Cell_Telephone, Home_Telephone) 
AS SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last), 
phone_dorm, phone_home
FROM students;

If you look at the example for CREATE VIEW in our MySQL documentation you'll see that we're adding an extra column to the view created in that example. The other settings remain unchanged.

You cannot change the name of an existing view in MySQL. Instead, use the DROP VIEW statement and then create a new view with the CREATE VIEW statement.