How to create a view and update it later?

Question

Is it possible to update the same view with new data? Using UPDATE after CREATE didn't seem to work.

Have only 1 table. Want the view to be a subset of that table. After using the view, I would like the same view to hold a different subset of the data from the only table.

Was thinking I can create the view, drop it, then create it again with the same name ut different subset from the table.....but not sure if there is a better way?

Create view ID 1-10 if it does not exist.
. 
. //
. //
. 
Update view **ID** 2-10   

Any help is appreciated.

Answer 1

Yes, updates to base tables will be visible in a view (all other things being equal). More information would be helpful.

Answer 2

Views are the wrong tool for this.

You should probably make a stored procedure that takes the ID range as a parameter.

To answer the question, you're looking for the ALTER VIEW statement.

Answer 3

I think you are misunderstanding the purpose of a view. What you are trying to do can be handled with a simple select by changing the WHERE clause. A view normally represents a fixed window into the table (or tables) defined by its selection criteria. You wouldn't normally go changing the view dynamically to represent different selection criteria. Normally, you'd simply do a select either against the table or against the view itself, if you're selecting a subset of the columns in the view or doing a join of multiple tables in the view. Since you have a single table, I'd suggest just constructing the query you need dynamically and skipping the view entirely.

  select * from table where ID > 0 and ID <= 10

then

  select * from table where ID > 1 and ID <= 10

Note that in many cases you could save this as a stored procedure and parameterize the query if need be. If your language/framework supports it, use parameterized queries when issuing simple commands as well.