SQL Updating a View is not working

Other SQL Server Topics
1

--SQL generates an error KEYWORD OR what is the correct syntax
--Source:http://www.w3schools.com/sql/sql_view.asp

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

2

are you using Microsoft SQL Server or other dbms ?

MS SQL Server does not support CREATE OR REPLACE.

You will need to DROP the view and CREATE again

1 Like
3

If the VIEW already exists you can use ALTER.

If the view does not exist you can create a "stub" for it, and then use ALTER (instead of using DROP / CREATE).

Possible benefit of ALTER is that it does not drop the view until the create is successful, so if there is a syntax error in your code which prevents the new view being created then the old view will be left in place. (This is good and bad!! For updates on a live system existing users are not effected if there is a syntax error, in a large script you might not notice an error message failing to create the View, but the old view will be left in place causing a hard-to-find bug thereafter)

ALTER retains all permissions on the existing view.

If you want to create a "Stub" you coudl do something like this

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'MyViewName')
	EXEC ('CREATE VIEW dbo.MyViewName AS SELECT [StubViewOnly]=1')
GO

I find that W3Schools created more questions than it answers ...

1 Like
4

:blush: how can that skipped my mind. Probably because i don't alter. I drop and create.Something that carry forward from old version of SQL Server. There wasn't any "modified date" in the system objects, only "create date". DROP and CREATE is a way to record when the view was last changed.

Wonder CREATE OR REPLACE is it ANSI ? Maybe they should stick to ANSI, it should works on all flavor of RDBMS. (maybe not all but most :smile:)

5

I am using SQL Server A product of Microsoft

http://www.w3schools.com/sql/sql_view.asp
The Update View Syntax is given on this link but not working.
therefor I post it over here..
ok thanks for your reply.
Mr.Khtan

6

We have scripts that CREATE (or ALTER, whatever!) objects. One script per object. That script also has any necessary GRANT of permissions, and "create config data" or anything else like that. It also have an SProc call (with name of object plus Version No) at the start of the script to log the fact that the script was run / object was (re)created.

So it doesn't really make much difference to me whether I DROP/CREATE or ALTER.

We changed operating-procedure a few years ago from DROP/CREATE to ALTER, mainly so that if we had to run a script on a Production system it was less "intrusive" than DROP/CREATE (which would leave the object missing for a short time, or missing altogether if there was a syntax error!).

But our rollout scripts concatenate ALL the individual object scripts (changed since previous version) and we now find that there is a very real risk that one/many ALTER fails and the old, original, object is left in place (AND we miss the error message in the huge long list of "Worked OK" messages!!). Previously with DROP/CREATE the object would have been physically missing, and we would have picked that up.

Not sure which is better ...

Actually, typing this, it occurs to me that I could check for MODIFY DATE < Most Recent Logged Date - that should show up any object which failed to ALTER. (Our DBA Reports which use the Logging Table ALREADY warn about "Object appears to be older than the log record" ... so it should have been obvious to me to use that as a rollout double-check.

Thanks KHTan ... I'll send you a beer! :chug:

1 Like
7

Yes, W3Schools does list the ANSI syntax etc., but that's what I mean about "creates more questions than it answers". If that were my site I would list whether each syntax example was supported by MSSQL, MySQL, Oracle etc. AND I would describe what alternatives were available (such as ALTER) AND provide examples for the various scenarios where there are issues - rather than just the "one very generic" example that they show. Newbies wouldn't have to read the detail if they didn't want to, but people arriving there on a Google Search looking for a solution to a specific problem would more likely be better served, and broaden their knowledge - which surely is the objective of any "school"? ...

1 Like
8

if you are looking for information about SQL syntax specifically for MS SQL I think you would be much better off reading the Microsoft site, for example:

I think W3Schools is far too "broad" to be useful in specific cases/

1 Like
9

You could refer the microsoft link to create view and make it to allow DML commands

https://msdn.microsoft.com/en-us/library/ms180800.aspx#Restrictions

10

Drop and re-create is vastly more complex than simply altering the existing view. Dropping the view could invalidate other objects, besides losing permissions and any extended properties associated with the object.

I'd suggest always using ALTER rather than the other way around if you use only a single method.

For example:

IF OBJECT_ID('view_name') IS NULL
    CREATE VIEW view_name AS SELECT GETDATE() AS current_date;
GO
ALTER VIEW view_name AS ...
1 Like
11

Great ! I was wondering how are you going to use ALTER in one single script

12

Personally I would suggest using something that more obviously indicates that the VIEW is a stub (i.e. for the situation where the ALTER then fails)

13

It was just a sample of the technique needed. Obviously you would tailor the code to indicate the proc name, etc..