ALTER VIEW

ALTER VIEW - change the definition of an existing view

Syntax

ALTER VIEW [schema.]view [ ( column_name [ ,column_name ]... ) ] AS SELECT query

Description

ALTER VIEW uses the exact same syntax as CREATE VIEW (see CREATE VIEW) and will succeed only if the view already exists. To change a view, you need to supply the SELECT statement that defines the view. You can completely change the definition of the view, including the view columns, the columns in the select list, one or more of the tables being selected in the from clause, the where clause, etc. (see SELECT). This command combines the functionality of the DROP VIEW and CREATE VIEW statements.

Using the INVOKER security model, it does not alter the owner of the view or modify the privileges that have been defined on the specific view.
Using the DEFINER security model, it does not modify the privileges that have been defined on the specified view for all the users; however, the running user becomes the owner of the view with full privileges.

Parameters

schema: Name of the schema in which the view to be altered must already exist. The default is the current schema as defined by the USE command.
view: Name (optionally schema-qualified) of an existing view to be altered.
column_name: An optional list of column names to be used as columns for the view. If specified, these names override the column names that would be based on the SELECT query. Each column_name defined for a view must be unique.
query: A SELECT statement (see SELECT) which provides the columns and rows of the view. If the optional column_name list is not provided when creating the view, the column names defined for the view are the same as the SELECT statement column_list names.

If joining multiple tables, and the SELECT statement column_list includes a column that is common to multiple joined tables, the column in the SELECT statement column_list must be qualified by a table name or, if used, table name alias.

If the optional column_name list is not provided when creating the view, and the SELECT statement column_list includes duplicate column names, those duplicate names must be assigned alias column names, using AS. All column names defined in a view must be unique.

Example

Create a view and alter the view

USE hockey;
CREATE VIEW vw_berg_players AS SELECT * FROM hockey.hockey where name like '%BERG%';
SELECT * FROM vw_berg_players;
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
  1    37    PATRICE BERGERON   Forward  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins

ALTER VIEW vw_berg_players AS SELECT id, number, name FROM hockey.hockey where name like '%BERG%';
SELECT * FROM vw_berg_players;
 ID  NUMBER        NAME
 --- ------- -----------------
  1    37    PATRICE BERGERON
 21    44    DENNIS SEIDENBERG