1
00:00:00,120 --> 00:00:05,520
The next thing we need to cover around views is what we can and can't do with views.

2
00:00:05,550 --> 00:00:07,710
Views may act like a table.

3
00:00:07,710 --> 00:00:12,960
They might look like a table in some situations, but of course they're not true tables behind the scenes.

4
00:00:12,960 --> 00:00:17,400
And that means we can't always perform all table operations with a given view.

5
00:00:17,700 --> 00:00:28,350
So if we go back to our full select start from full reviews view, this view here has all sorts of information

6
00:00:28,530 --> 00:00:30,230
synthesized from different tables.

7
00:00:30,240 --> 00:00:36,330
Of course, we joined three tables together in the previous video, so with a regular table, if this

8
00:00:36,330 --> 00:00:39,360
is a true table, I could do something like delete.

9
00:00:40,110 --> 00:00:44,310
Let's just delete everything that was released in the year 2010.

10
00:00:44,340 --> 00:00:46,620
I don't know why, but let's say that's what I want to do.

11
00:00:46,890 --> 00:00:54,510
Well, in a regular table I would do a delete from the table name Full reviews where released year equals

12
00:00:54,510 --> 00:00:57,780
2010, but it doesn't work.

13
00:00:57,780 --> 00:01:02,340
It says cannot delete from join view called full review.

14
00:01:02,340 --> 00:01:04,620
So you can't delete from that view.

15
00:01:05,010 --> 00:01:09,750
So what it's telling me is it's not a real table and there's rules to what we can and can't do.

16
00:01:09,750 --> 00:01:13,980
And because this is a join view, you can't delete.

17
00:01:14,340 --> 00:01:18,660
So it's a little complicated, the different rules, but there's a whole page in the docs about this.

18
00:01:19,380 --> 00:01:26,940
Basically only a very small portion of views are updatable and insert double meaning we can insert into

19
00:01:26,940 --> 00:01:30,270
them and update them and treat them as if they were a table.

20
00:01:30,300 --> 00:01:31,740
Same thing with delete.

21
00:01:32,400 --> 00:01:38,460
And if you have any of these bullet points in your view, then you're going to have a bad time if you

22
00:01:38,460 --> 00:01:39,960
use an aggregate function.

23
00:01:39,960 --> 00:01:47,280
Some in Macs that view is no longer updatable or delete able or inscrutable.

24
00:01:47,280 --> 00:01:53,850
If you use group by, if you have some queries, if you have joints, at least certain joints.

25
00:01:53,850 --> 00:01:56,610
And this is a nuance thing, there's a lot of detail about joints.

26
00:01:56,610 --> 00:02:01,740
Sometimes you can write a joint view that is updatable, but we're not going to go into all of that.

27
00:02:01,740 --> 00:02:06,540
It can get quite complicated, but the point is that our view is not updatable.

28
00:02:06,540 --> 00:02:08,250
It's not going to work for us.

29
00:02:08,250 --> 00:02:13,560
But that doesn't mean all views are limited to only selecting.

30
00:02:13,560 --> 00:02:18,870
We could write a simple view, just as an example here that I don't know.

31
00:02:18,870 --> 00:02:20,340
Let's do like a query.

32
00:02:20,340 --> 00:02:22,440
We might run somewhat frequently.

33
00:02:22,440 --> 00:02:30,690
Let's do select star from series and we'll do an order by released here.

34
00:02:30,720 --> 00:02:32,790
Let's say this is something we do a lot.

35
00:02:32,790 --> 00:02:35,040
Probably not, but let's say it is.

36
00:02:35,190 --> 00:02:37,080
I could make this a view.

37
00:02:37,560 --> 00:02:42,540
I'll call it something like, I don't know, create view.

38
00:02:43,170 --> 00:02:44,550
What should we call it?

39
00:02:45,490 --> 00:02:47,500
Ordered series.

40
00:02:48,040 --> 00:02:49,270
Again, it's silly.

41
00:02:49,270 --> 00:02:55,060
It's trivial because we could just this is a short enough query to run, but I want to show just a simple

42
00:02:55,060 --> 00:03:01,630
example to prove my point that some queries or that some views are updatable and in searchable OC.

43
00:03:01,750 --> 00:03:11,860
So if I create this view ordered series now I select star from ordered series ordered series.

44
00:03:12,640 --> 00:03:13,260
There we are.

45
00:03:13,270 --> 00:03:15,640
We see all of our series ordered by release here.

46
00:03:15,880 --> 00:03:16,920
Simple enough.

47
00:03:16,930 --> 00:03:22,750
So let's say I treat this as a table ordered series and I want to delete or actually I don't want to

48
00:03:22,750 --> 00:03:25,920
delete our data, but let's say I want to insert something into it.

49
00:03:25,930 --> 00:03:32,020
I really like the show The Great, which came out I think in 2020 for the first season.

50
00:03:32,020 --> 00:03:37,750
So I would do an insert into never mind the fact that I could just insert into series with this.

51
00:03:37,750 --> 00:03:47,800
Just let's ignore that, let's just insert into ordered series and I'll do a title released year and

52
00:03:47,800 --> 00:03:48,790
genre.

53
00:03:49,800 --> 00:03:51,840
And then have my values.

54
00:03:52,350 --> 00:04:00,540
The Great 2020 is the first release year and genre is comedy, and that needs to be in quotes.

55
00:04:02,370 --> 00:04:05,880
So this is not a real table ordered series.

56
00:04:05,910 --> 00:04:11,090
Of course, behind the scenes there is a table called series, but this is a view that we set up.

57
00:04:11,100 --> 00:04:17,730
It looks like a table that it's not a true table, but it will let us insert because this is considered

58
00:04:17,730 --> 00:04:24,810
an instable, updatable, delectable view, and that's because it doesn't break any of the rules laid

59
00:04:24,810 --> 00:04:25,470
out here.

60
00:04:25,470 --> 00:04:26,700
We didn't use Group BI.

61
00:04:26,700 --> 00:04:28,140
We don't have aggregate functions.

62
00:04:28,140 --> 00:04:30,450
There's no sub queries, we don't have joints.

63
00:04:30,450 --> 00:04:34,020
So it is an updatable view I can insert into it.

64
00:04:34,020 --> 00:04:40,920
And now if I do a select star from ordered series, we see the great edit in there and that means it's

65
00:04:40,920 --> 00:04:44,910
also been added into select star from series.

66
00:04:45,640 --> 00:04:54,040
Into our series so I can also delete delete from ordered series where.

67
00:04:54,550 --> 00:04:59,950
Let's do well let's just do title equals the great to delete that.

68
00:05:00,850 --> 00:05:02,470
And one more time.

69
00:05:02,470 --> 00:05:04,120
This is our virtual table.

70
00:05:04,120 --> 00:05:06,370
This is not a true table.

71
00:05:06,400 --> 00:05:11,650
This is all based upon the query that we stored under the View name ordered series.

72
00:05:11,650 --> 00:05:14,440
But I can treat it as a table and I can delete from it.

73
00:05:15,010 --> 00:05:20,440
And if we now look at ordered series, the Great is gone.

74
00:05:20,680 --> 00:05:21,940
Well, it should be gone.

75
00:05:21,940 --> 00:05:22,840
And it is gone.

76
00:05:23,260 --> 00:05:25,630
Okay, so that's all I'm going to show in this video.

77
00:05:25,870 --> 00:05:31,780
Just know that by making something a view that does not mean that you can truly treat it as a table

78
00:05:31,780 --> 00:05:33,610
with all the standard operations.

79
00:05:33,610 --> 00:05:39,430
Deleting, updating, inserting only a subset of views allow those operations and it all depends on

80
00:05:39,430 --> 00:05:41,740
if they include any of these features or not.