Create View from View? What's wrong?

Question

4.00/5 (1 vote)

See more:

I have 2 Views, the View1 is created first, and then View2 is created like as
CREATE VIEW View2 AS SELECT * FROM View1
Now, I drop View1 by DROP VIEW View1
I think all data in View1 has been save into View2, but it doesn't seem like that.
When I select data from View2, an error will be made, for example, if I execute a query like "SELECT * FROM View2", the error will say "The Microsoft Access database engine cannot find the input table or query 'View1'..."
I don't understand, my query relates to View2 only, HOW DOES IT WORK?
Could you please explain that, if possible, please give me another solution, I want to save data somewhere before dropping the View1?
Thank you so much!

Posted 14-Apr-11 4:51am

King Boy

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Not Active · Accepted Answer · 2011-04-14T05:10:00

A view is simply, ...well a view, of the data specified by the query used to form it. It is not a table and not stored in a table. If you drop view1 its gone you can't reference in in other views or queries. A view is also not about saving data, the data is already present in the table(s) that are referenced. If you stored the data extracted by the view somewhere else, in another table for instance, it would be disconnected and static.

**R. Giskard Reventlov** · Accepted Answer · 2011-04-14T05:06:00

Solution 1

If you refer to View1 in View2 and then drop View1 how did you expect View2 to work? It is dependent upon View1. A view is not a table: it contains no data of it's own: the data is in whichever table you are referring to. Perhaps don't drop View1 or base View2 on the source table of View1 (which begs the question what is the point of View2?)

Since it is difficult to work out what you are really trying to do this is about the best I can do for now.

Posted 14-Apr-11 5:06am

R. Giskard Reventlov

Comments

[no name] 14-Apr-11 11:12am

Thank you! I understand! :)
Just a little confusion about the definition "VIEW"!

[no name] 14-Apr-11 11:18am

Ah, could you please tell me if we have any way to update "data" that a view refers to, I mean whenver referring to a new data, do I have to recreate the view? That's why I want to drop View1 before creating a new instance of "View1" that refers to a new data (updated).
Thank you so much!

R. Giskard Reventlov 14-Apr-11 11:23am

You'd create and run a stored procedure that would do this work for you. Like:

update table1
set column1 = @Param1
where column2 = @Param2

where @Param1 is the new value
and @Param2 represents a value upon which to filter the data. This is pretty basic stuff.

[no name] 14-Apr-11 11:40am

Your "update table1" is to update a table, isn't it?
But I don't want to change data in my table, just the 'data' in View1, for example, first my View1 refers to Column1 of my table, then I want it to refer to Column2 of my table, my table doesn't change anything, just the "data" in my View1. Normally, I have to update like this:
1. Store "data" View1 refers to somewhere.
2. Drop View1
3. CREATE VIEW View1 as SELECT Column2 from mytable
That's my idea!
Thank you so much!

R. Giskard Reventlov 14-Apr-11 12:36pm

NO! You update data in the table. The view is just that, a view on the data. You really need to do some basic research: go read some books about databases.

[no name] 14-Apr-11 14:51pm

I know, you may not understand my idea, I don't need to change data in my table (or update), the thing I want to update is the View1. As you explained, it refers to some data, and when I say "update View1" is that I want it to refer to another data. How can we do that easily?
The way I know is to DROP View1 first and then REFER it to another data by RECREATE IT, the new reference is pointed at "...AS Select ..." in the command creating View.
And what I'm wondering is how to do that (change the reference, of course not related to any parts of my data at all) without DROPING View1 first and RECREATING View1 then?
I hope you understand what I mean!
Thank you so much!

R. Giskard Reventlov 15-Apr-11 2:08am

No clue:you can only update View1 if the data in the table that it is sourced from chnages. I really don't think youget how any of this works.

[no name] 15-Apr-11 23:02pm

I think you see a View once created will be "died" after dropping, and the only way to "update" it (as I explained my conception about "update" a View) is CHANGING the data it refers to. But I see it will never die until we rename it, I mean after recreating It is still itself with the name "View1" as a constant to use in any code parts needing to handle with "View1".
Thank you!