ALTER VIEW - change the definition of an existing view
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
This command combines the functionality of the
DROP VIEW and
CREATE VIEW statements.
INVOKERsecurity model, it does not alter the owner of the view or modify the privileges that have been defined on the specific view.
DEFINERsecurity 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.
Name of the schema in which the view to be altered must already exist. The default is the current schema as defined by the
Name (optionally schema-qualified) of an existing view to be altered.
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
column_namedefined for a view must be unique.
SELECT) which provides the columns and rows of the view. If the optional
column_namelist is not provided when creating the view, the column names defined for the view are the same as the
If joining multiple tables, and the
column_listincludes a column that is common to multiple joined tables, the column in the
column_listmust be qualified by a table name or, if used, table name alias.
If the optional
column_namelist is not provided when creating the view, and the
column_listincludes duplicate column names, those duplicate names must be assigned alias column names, using
AS. All column names defined in a view must be unique.
- 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