The last thing I'll show you with views is how to actually change the view itself, the query that is
run by your view.
So I have this really simple one right here ordered series.
And let's say that I made a mistake and I want it to order by the other direction in descending order.
Never mind the fact that I could just add an order by on at the end if I do select star from ordered
series.
But let's say there's some huge view, some crazy query.
I want to update it.
My data has changed.
The name of a table has changed columns have changed, something where I need to update a view.
It's not that uncommon.
So to do this, you might think that I could just create the view again with the same name.
So if this is my original one, this new one, the new version of ordered series just has descending.
If I try and run this query now to create that view again, I get an error.
It says that already exists in the same way.
If I tried to make a real table again with the same name, I'll get that error.
It's not a real table, but it's being treated as a table in this situation.
So what I can do instead is slightly modify this to be create or replace view ordered series as blah
blah blah.
So if it doesn't exist, create it.
If it does exist, replace the view with this new query.
So if I run this now, it works.
Or at least it doesn't give me an error.
And if I run my select star from ordered series.
Ordered series.
The order has changed.
Right now it's going from 2016 down to 1963.
It is in descending order.
Very simple example, probably not a change you would really need to make.
But if I don't know, maybe you do need to change it to be descending order.
That's how you would do it.
But more often than not, we might be working with something like this where we eventually solidify
our schema and we decide that released here is not a good name for the column on the series table,
so we might rename that to yea, which means my view is going to break unless I update the view to have
the correct column name.
And the way to update that is at least one way is create or replace because you can't just recreate
it if it already exists.
We also have the option to use alter view, so alter view followed by whatever we're trying to alter.
Let's say it's the ordered series view as and then we just put our new definition.
So let's go back to having it not in descending order.
Silly, but let's do it.
Run this query again.
Now select star from ordered series.
Series.
And we go back to the ascending order 63 up to 2016.
So that is alter view.
That's another option instead of create or replace.
And then finally, I'll show you how to drop a view.
It's as simple as drop view ordered series, and that's it.
And now if I try and select star from ordered series, it doesn't work.
But that does not mean we deleted the data.
Right?
The series table is still here.
Select star from series.
We just dropped The View and that's a very important distinction.
And that is it for View's.