ALTER VIEW (SQL)

Synopsis

ALTER VIEW viewName AS query
ALTER VIEW viewName (column, column2, ...) AS query

ALTER VIEW viewName ... AS query WITH READ ONLY
ALTER VIEW viewName ... AS query WITH [LOCAL | CASCADED] CHECK OPTION

Description

The ALTER VIEW command modifies views created using the CREATE VIEW command or a view projected from a persistent class. The altered view replaces the existing view, so you cannot modify specific columns in a view.

A view is a virtual table based on the result set of a SELECT query or a UNION of such queries. For more details on views, see Defining and Using Views.

• ALTER VIEW viewName AS query replaces the existing columns in a view with the columns returned by the SELECT query. The view column names are derived from the column names returned by the result set of the query, which can be:

  • The column names or aliases of the table or view being queried

  • The column name of a class query defined as a table-valued function

  This statement modifies the NewEmployees view so that it includes only employees hired within the last 12 months. The view column names, Name, Office, and StartDate, match the column names of the source table.

  ALTER VIEW NewEmployees AS
       SELECT Name,Office,StartDate
       FROM Sample.Employees
       WHERE DATEDIFF('month',StartDate,CURRENT_DATE) <= 12

  

• ALTER VIEW viewName (column, column2, ...) AS query specifies the names of the columns to include in the view. The column names must correspond in number and sequence with the table columns returned by the SELECT query. Alternatively, you can specify these view column names as column name aliases in the SELECT statement query. These ALTER VIEW statements are equivalent:

  ALTER VIEW MyView (MyViewCol1,MyViewCol2,MyViewCol3) AS
       SELECT TableCol1, TableCol2, TableCol3 FROM MyTable

  

  ALTER VIEW MyView AS SELECT TableCol1 AS ViewCol1,
       TableCol2 AS ViewCol2,
       TableCol3 AS ViewCol3
       FROM MyTable

  

  The column specification replaces any existing columns specified for the view.

  Example: Create and Alter a View

• ALTER VIEW viewName ... AS query WITH READ ONLY specifies that no insert, update, or delete operations can be performed through this view upon the table on which the view is based. The default is to permit these operations through a view, subject to any specified WITH CHECK OPTION constraints.

  Example: Set Read-Only View

• ALTER VIEW viewName ... AS query WITH [LOCAL | CASCADED] CHECK OPTION checks that any row being updated or inserted into this view satisfies the WHERE constraints of the view. If the row does not meet these constrains, that row is not updated or inserted. You can specify these check options:

  • WITH LOCAL CHECK OPTION — Check only the WHERE clause of the view specified in the INSERT or UPDATE statement.

  • WITH CASCADED CHECK OPTION or WITH CHECK OPTION — Check the WHERE clause of the view specified in the INSERT or UPDATE statement and all underlying views on which that view is based. This option overrides any WITH LOCAL CHECK OPTION clauses in these underlying views and is recommended for all updateable views.

  For more details on these options, see The WITH CHECK Option.

  Example: Validate Table Modifications Made Through a View

Arguments

Examples

Create and Alter a View

This example shows how to create a view and then alter it. The example also shows how to query and delete the view.

Create a view the contains the names of people who live in Massachusetts. This example assumes that a Sample.Person table already exists and contains a Home_State column.

CREATE VIEW MassFolks (vFullName) AS 
    SELECT Name FROM Sample.Person WHERE Home_State='MA'

You can then query the view as you would a regular table.

SELECT * FROM MassFolks

Modify the view to include new columns. Altering a view replaces the column list with a new column list but does not preserve the prior column list. Therefore, this modified view contains only the vMassAbbrev and vCity columns, not the vFullName column.

ALTER VIEW MassFolks (vMassAbbrev,vCity) AS
     SELECT Home_State,Home_City FROM Sample.Person WHERE Home_State='MA'

Delete the view. You can delete a view similar to how you would delete a regular table.

DROP VIEW MassFolks

Alter View Using Combined SELECT Queries

Alter a view to include the combined results of two SELECT queries. To combine the results, you use a UNION command.

ALTER VIEW MyView (vname,vstate) AS
  SELECT t1.name,t1.home_state
    FROM Sample.Person AS t1
  UNION
  SELECT t2.name,t2.office_state
    FROM Sample.Employee AS t2

Set Read-Only View

Modify a view to prevent modifying the underlying table through this view.

ALTER VIEW YoungPeople AS
     SELECT Name,DOB
     FROM Sample.Person
     WHERE DATEDIFF(year,DOB,CURRENT_DATE) <= 18
WITH READ ONLY

If you update any row through this view, the WITH READ ONLY prevents the update.

UPDATE YoungPeople (DOB)
VALUES (02/17/2022)
WHERE Name='Page,Laura O.'

Validate Table Modifications Made Through a View

Modify this view of honor students to prevent the insertion of students that do not meet the GPA criteria. This examples assumes that a Sample.Student table already exists.

ALTER VIEW HonorsStudent AS
    SELECT Name, GPA
    FROM Sample.Student
    WHERE GPA > 3.0
WITH CHECK OPTION

If you try to insert a student with too low a GPA for this view, the VIEW CHECK OPTION prevents the insertion.

INSERT INTO HonorsStudent (Name, GPA)
VALUES ('Waal,Edgar P.',2.9)

Security and Privileges

The ALTER VIEW command is a privileged operation. The user must have %ALTER_VIEW administrative privilege to execute ALTER VIEW. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %ALTER_VIEW privileges.

The user must have %ALTER privilege on the specified view. If the user is the Owner (creator) of the view, the user is automatically granted %ALTER privilege for that view. Otherwise, the user must be granted %ALTER privilege for the view. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have privilege to ALTER the view 'Schema.ViewName'.

If you hold appropriate granting privileges, you can assign %ALTER_VIEW and %ALTER privileges by using the GRANT command.

To determine if the current user has %ALTER privileges, call the %CHECKPRIV command. To determine if a specified user has %ALTER privilege, call the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method.

In embedded SQL, you can use the $SYSTEM.Security.Login()Opens in a new tab method to log in as a user with appropriate privileges:

   DO $SYSTEM.Security.Login("myUserName","myPassword")
   &sql(...)

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.SecurityOpens in a new tab in the InterSystems Class Reference.

ALTER VIEW cannot be used on a view based on a table projected from a deployed persistent class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.

See Also

• CREATE VIEW, DROP VIEW, GRANT

• Defining Views

• SQLCODE error messages