Open topic with navigation
ALTER VIEW - change the definition of an existing view
ALTER VIEW [
column_name]... ) ] AS SELECT
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, except that it does not alter the owner of the view or modify the privileges that have been defined on the specified view.
Caution: If DDL statements are to be executed concurrently with DML statements, ensure that a
LOCK TABLE command is successfully executed prior to executing the DDL statement.
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_name defined for a view must be unique.
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
If joining multiple tables, and the
column_list includes a column that is common to multiple joined tables, the column in the
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
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.
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