Confused by Syntax for updating a View

Question

On this site http://www.w3schools.com/sql/sql_view.asp it says that I can update a view as follows:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

However, I created the view shown below:

CREATE VIEW ProductCustomers AS    
SELECT cust_name, cust_contact, prod_id     
FROM Customers, Orders, OrderItems      
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num

And then tried to update it via the Syntax suggested:

CREATE VIEW ProductCustomers AS    
SELECT cust_name, cust_contact, prod_id,cust_address --JUST ADDING THE COLUMN cust_address
FROM Customers, Orders, OrderItems      
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num

I get the Error: "There is already an object named 'ProductCustomers' in the database.There is already an object named 'ProductCustomers' in the database." so I'm unsure how to add a new Column to my view.

Thanks

Answer 1

Here's a pragmatic approach that I like to use when defining my objects (in this case, a view).

if object_id('[dbo].[viewName]') is not null --object already exists
   set noexec on;
go
create view [dbo].[viewName] as
   select 'not implemented' as message
go
set noexec off;
go
alter view [dbo][viewName] as
--your actual view definition here

By way of explanation, this code checks to see if the view already exists. If it doesn't already exist, a stub is created. Once the stub creation section is either executed or skipped, a view by that name is guaranteed to exist, and so an alter view statement is guaranteed to work.

Answer 2

DROP VIEW ProductCustomers
GO
CREATE VIEW ProductCustomers AS   
SELECT cust_name, cust_contact, prod_id,cust_address     FROM Customers, Orders, OrderItems     
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
Go

Answer 3

you need to use ALTER VIEW

ALTER VIEW ProductCustomers AS