Updating a View

Once you've defined a view, you might at some point want to modify its definition. To do this, you use the following statement in SQL Server:

ALTER VIEW ViewName AS NewQuery

In Oracle you use slightly different syntax to alter the definition of a view:

CREATE OR REPLACE VIEW ViewName AS NewQuery

The ALTER VIEW statement in Oracle performs a different action to the one used in SQL Server. You can use the ALTER VIEW statement to recompile a view or alter its constraints. For more information, you should check the Oracle documentation.

In DB2 there's also an ALTER VIEW statement; however, its meaning is quite different. This statement allows you to modify individual columns in a view, making them refer to additional data. The simplest way to change a view in DB2 is to delete it and add it again.

CHANGING A VIEW

In SQL Server, execute the following statement:

ALTER VIEW ClassAttendees AS
SELECT Class.ClassID, NULL AS StudentID, SUBSTRING(Professor.Name,
       LEN(Professor.Name) - CHARINDEX(' ', REVERSE(Professor.Name)) + 2, 100)
       + ', '
       + LEFT(Professor.Name,
               LEN(Professor.Name) - CHARINDEX(' ', REVERSE(Professor.Name)))
       AS Name, 'Professor' AS Role
FROM Professor INNER JOIN Class ON Professor.ProfessorID = Class.ProfessorID
UNION
SELECT Enrollment.ClassID, Student.StudentID, SUBSTRING(Student.Name,
       LEN(Student.Name) - CHARINDEX(' ', REVERSE(Student.Name)) + 2, 100)
       + ', '
       + LEFT(Student.Name,
              LEN(Student.Name) - CHARINDEX(' ', REVERSE(Student.Name)))
       AS Name, 'Student' FROM Student
       INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID;

For Oracle, change the first line to the following:

CREATE OR REPLACE VIEW ClassAttendees AS

Then execute the following statement:

SELECT ClassID, StudentID, Name, Role FROM ClassAttendees;

to display the amended view:

   ClassID  StudentID  Name            Role

   -------  ---------  --------------  ---------
   1        NULL       Dawson, Prof.   Professor
   1        1          Jones, John     Student
   1        5          Wolff, Anna     Student
   1        8          Picard, Julia   Student
   2        NULL       Dawson, Prof.   Professor
   2        1          Jones, John     Student
   ...      ...        ...             ...

Here you've modified the view created in the last example, adding a new StudentID column. For contacts in the Student table, you have a column called StudentID that you can look at via this view, but no such information exists for the Professor table, so you simply insert a NULL value.