1
00:00:00,210 --> 00:00:06,390
The last thing I'll show you with views is how to actually change the view itself, the query that is

2
00:00:06,390 --> 00:00:08,160
run by your view.

3
00:00:08,520 --> 00:00:11,970
So I have this really simple one right here ordered series.

4
00:00:11,970 --> 00:00:18,780
And let's say that I made a mistake and I want it to order by the other direction in descending order.

5
00:00:18,870 --> 00:00:24,090
Never mind the fact that I could just add an order by on at the end if I do select star from ordered

6
00:00:24,090 --> 00:00:24,690
series.

7
00:00:24,870 --> 00:00:28,860
But let's say there's some huge view, some crazy query.

8
00:00:28,860 --> 00:00:29,840
I want to update it.

9
00:00:29,850 --> 00:00:31,200
My data has changed.

10
00:00:31,200 --> 00:00:35,430
The name of a table has changed columns have changed, something where I need to update a view.

11
00:00:35,520 --> 00:00:36,870
It's not that uncommon.

12
00:00:37,290 --> 00:00:43,170
So to do this, you might think that I could just create the view again with the same name.

13
00:00:43,350 --> 00:00:50,430
So if this is my original one, this new one, the new version of ordered series just has descending.

14
00:00:50,670 --> 00:00:56,040
If I try and run this query now to create that view again, I get an error.

15
00:00:56,040 --> 00:00:58,410
It says that already exists in the same way.

16
00:00:58,410 --> 00:01:03,400
If I tried to make a real table again with the same name, I'll get that error.

17
00:01:03,420 --> 00:01:07,080
It's not a real table, but it's being treated as a table in this situation.

18
00:01:07,080 --> 00:01:17,250
So what I can do instead is slightly modify this to be create or replace view ordered series as blah

19
00:01:17,250 --> 00:01:17,790
blah blah.

20
00:01:18,060 --> 00:01:20,310
So if it doesn't exist, create it.

21
00:01:20,340 --> 00:01:25,050
If it does exist, replace the view with this new query.

22
00:01:25,740 --> 00:01:29,190
So if I run this now, it works.

23
00:01:29,190 --> 00:01:30,660
Or at least it doesn't give me an error.

24
00:01:30,660 --> 00:01:35,610
And if I run my select star from ordered series.

25
00:01:36,510 --> 00:01:38,640
Ordered series.

26
00:01:40,320 --> 00:01:41,310
The order has changed.

27
00:01:41,350 --> 00:01:45,160
Right now it's going from 2016 down to 1963.

28
00:01:45,180 --> 00:01:46,680
It is in descending order.

29
00:01:46,710 --> 00:01:50,790
Very simple example, probably not a change you would really need to make.

30
00:01:50,790 --> 00:01:53,760
But if I don't know, maybe you do need to change it to be descending order.

31
00:01:53,760 --> 00:01:54,900
That's how you would do it.

32
00:01:54,900 --> 00:02:01,290
But more often than not, we might be working with something like this where we eventually solidify

33
00:02:01,290 --> 00:02:09,539
our schema and we decide that released here is not a good name for the column on the series table,

34
00:02:09,539 --> 00:02:15,270
so we might rename that to yea, which means my view is going to break unless I update the view to have

35
00:02:15,270 --> 00:02:16,410
the correct column name.

36
00:02:17,010 --> 00:02:23,130
And the way to update that is at least one way is create or replace because you can't just recreate

37
00:02:23,130 --> 00:02:24,450
it if it already exists.

38
00:02:24,450 --> 00:02:32,460
We also have the option to use alter view, so alter view followed by whatever we're trying to alter.

39
00:02:32,460 --> 00:02:37,680
Let's say it's the ordered series view as and then we just put our new definition.

40
00:02:37,680 --> 00:02:42,510
So let's go back to having it not in descending order.

41
00:02:43,400 --> 00:02:45,050
Silly, but let's do it.

42
00:02:45,080 --> 00:02:46,490
Run this query again.

43
00:02:46,850 --> 00:02:50,040
Now select star from ordered series.

44
00:02:50,340 --> 00:02:51,800
Series.

45
00:02:52,610 --> 00:02:57,380
And we go back to the ascending order 63 up to 2016.

46
00:02:58,100 --> 00:02:59,690
So that is alter view.

47
00:02:59,690 --> 00:03:02,210
That's another option instead of create or replace.

48
00:03:02,420 --> 00:03:04,910
And then finally, I'll show you how to drop a view.

49
00:03:04,910 --> 00:03:09,190
It's as simple as drop view ordered series, and that's it.

50
00:03:09,200 --> 00:03:12,590
And now if I try and select star from ordered series, it doesn't work.

51
00:03:12,590 --> 00:03:15,740
But that does not mean we deleted the data.

52
00:03:15,740 --> 00:03:16,460
Right?

53
00:03:16,610 --> 00:03:18,500
The series table is still here.

54
00:03:18,500 --> 00:03:20,120
Select star from series.

55
00:03:20,330 --> 00:03:24,080
We just dropped The View and that's a very important distinction.

56
00:03:25,100 --> 00:03:26,360
And that is it for View's.