1
00:00:00,000 --> 00:00:04,725
Hello and welcome to the UPDATE statement and the DELETE statement.

2
00:00:04,725 --> 00:00:07,070
In this video, we will learn about altering and

3
00:00:07,070 --> 00:00:09,805
deleting data in a relational database table.

4
00:00:09,805 --> 00:00:11,390
At the end of this lesson,

5
00:00:11,390 --> 00:00:14,840
you will be able to identify the syntax of the UPDATE statement and

6
00:00:14,840 --> 00:00:19,830
DELETE statement and explain the importance of the WHERE clause in these statements.

7
00:00:19,830 --> 00:00:22,905
After a table is created and populated with data,

8
00:00:22,905 --> 00:00:26,705
the data in a table can be altered with the UPDATE statement.

9
00:00:26,705 --> 00:00:32,065
The UPDATE statement is one of the data manipulation language or DML statements.

10
00:00:32,065 --> 00:00:35,645
DML statements are used to read and modify data.

11
00:00:35,645 --> 00:00:38,335
Based on the author entity example,

12
00:00:38,335 --> 00:00:40,795
we created the table using the entity name

13
00:00:40,795 --> 00:00:44,945
Author and the entity attributes as the columns of the table.

14
00:00:44,945 --> 00:00:48,955
Rows were added to the Author table to populate the table.

15
00:00:48,955 --> 00:00:52,915
Sometime later, you want to alter the data in the table.

16
00:00:52,915 --> 00:00:55,920
To alter or modify the data in the Author table,

17
00:00:55,920 --> 00:00:57,735
we use the UPDATE statement.

18
00:00:57,735 --> 00:01:00,810
The syntax of the UPDATE statement looks like this,

19
00:01:00,810 --> 00:01:06,000
UPDATE [TableName] SET [ColumnName] = [Value] ]> <WHERE [Condition] >.

20
00:01:06,000 --> 00:01:09,765
In the statement, TableName identifies the table.

21
00:01:09,765 --> 00:01:13,245
The ColumnName identifies the column value to be changed,

22
00:01:13,245 --> 00:01:15,545
as specified in the <WHERE [Condition] >.

23
00:01:15,545 --> 00:01:18,195
Let's look at an example.

24
00:01:18,195 --> 00:01:23,190
In this example, you want to update the FIRSTNAME and LASTNAME of

25
00:01:23,190 --> 00:01:29,450
the author with AUTHOR_ID A2 from Rav Ahuja to Lakshmi Katta.

26
00:01:29,450 --> 00:01:32,830
In this example, to see the UPDATE statement in action,

27
00:01:32,830 --> 00:01:37,475
we start by selecting all rows from the author table to see the values.

28
00:01:37,475 --> 00:01:44,155
To change the first name and last name to Lakshmi Katta where the AUTHOR_ID = A2,

29
00:01:44,155 --> 00:01:46,435
enter the UPDATE statement as follows.

30
00:01:46,435 --> 00:01:49,570
UPDATE AUTHOR SET LAST NAME = KATTA,

31
00:01:49,570 --> 00:01:54,450
FIRST NAME = LAKSHMI WHERE AUTHOR_ID = A2.

32
00:01:54,450 --> 00:01:56,350
Now, to see the result of the update,

33
00:01:56,350 --> 00:02:00,080
select all rows again from the Author table and you will see that in row

34
00:02:00,080 --> 00:02:04,510
to the name changed from Rav Ahuja to Lakshmi Katta.

35
00:02:04,510 --> 00:02:07,455
Note that if you do not specify the WHERE clause,

36
00:02:07,455 --> 00:02:09,755
all the rows in the table will be updated.

37
00:02:09,755 --> 00:02:13,710
In this example, without specifying the WHERE clause all rows in

38
00:02:13,710 --> 00:02:18,155
the table would have changed the first and last names to Lakshmi Katta.

39
00:02:18,155 --> 00:02:23,000
Sometime later, there might be a need to remove one or more rows from a table.

40
00:02:23,000 --> 00:02:25,650
The rows are removed with the DELETE statement.

41
00:02:25,650 --> 00:02:27,200
The DELETE statement is one of

42
00:02:27,200 --> 00:02:31,175
the data manipulation language statements used to read and modify data.

43
00:02:31,175 --> 00:02:34,335
The syntax of the DELETE statement looks like this,

44
00:02:34,335 --> 00:02:37,180
DELETE FROM [TABLEName] <WHERE [Condition] >.

45
00:02:37,180 --> 00:02:40,955
The rows to be removed are specified in the WHERE condition.

46
00:02:40,955 --> 00:02:43,485
Based on the author entity example,

47
00:02:43,485 --> 00:02:48,140
we want to delete the rows for AUTHOR_ID A2 and A3.

48
00:02:48,140 --> 00:02:50,075
Let's look at an example.

49
00:02:50,075 --> 00:02:54,760
DELETE FROM AUTHOR WHERE AUTHOR_ID IN ('A2','A3').

50
00:02:54,760 --> 00:02:57,515
Note that if you do not specify the WHERE clause,

51
00:02:57,515 --> 00:03:00,290
all the rows in the table will be removed.

52
00:03:00,290 --> 00:03:03,730
Now you can identify the syntax of the UPDATE statement and

53
00:03:03,730 --> 00:03:08,845
DELETE statement and explain the importance of the WHERE clause in these statements.

54
00:03:08,845 --> 00:03:11,790
Thanks for watching this video.

55
00:03:11,790 --> 00:03:16,000
(Musi)