0:02
[Music]
0:12
hello friends welcome back to our
0:13
channel so in today's session we will
0:15
see two more comments in sql that is
0:19
update and delete
0:26
update
0:27
and
0:30
delete command
0:32
so coming to the first one that is a
0:34
update command
0:36
and it is used to
0:40
modify
0:43
the existing data
0:46
the existing
0:48
data so it is a slight difference with
0:50
the
0:52
alter command so alter will be the ddl
0:54
command and this update is a dml command
0:57
so
0:58
this one is a
0:59
dml
1:01
command
1:02
so dml command means here this command
1:05
will be used to change the data of a
1:07
table if it is a ddl it will change the
1:10
structure of a table
1:12
right so
1:14
the syntax for
1:16
update
1:18
is
1:19
update
1:24
table name
1:27
set
1:31
column 1 is equal to the new value
1:35
comma
1:38
column 2 is equal to a new value
1:43
comma etc
1:46
where
1:48
the condition satisfies
1:50
where the condition satisfies so first
1:53
it will check for the condition so for
1:55
all the resultant
1:57
rows
1:58
the column value will be set okay
2:01
whatever the value existing
2:03
that will be replaced with the
2:05
new value
2:06
okay the given value
2:08
so this is the syntax for update and
2:10
this is the same will be used for
2:12
updating either a single record or a
2:14
multiple records okay so based upon the
2:17
condition based upon the condition and
2:18
based upon the resultant table so it may
2:21
change the single row or multiple rows
2:25
so that's a update so i will demonstrate
2:27
this query i will execute this query in
2:29
the sql mysql okay so first let me
2:32
complete the syntax the theoretical
2:34
process and then we'll go to the
2:36
practical so i'll execute the query in
2:37
the mysql and i will show you
2:39
then coming to the delete
2:42
so delete is used to
2:45
delete the
2:46
rows specific rows of a table
2:51
so delete
2:55
use to
3:00
delete
3:05
specific rows
3:09
from a table
3:11
so some ah based upon the condition here
3:13
also will give some condition so based
3:15
upon the condition the particular rows
3:17
will be deleted either it may it can be
3:18
a single row or multiple row and similar
3:21
to our update function
3:22
update command this is also a dml
3:24
command
3:26
dmv command so this command is used to
3:29
change the data that means delete the
3:31
data and it is completely different from
3:33
drop and truncate
3:35
drop and truncate where drop and
3:37
truncate are the ddl commands data
3:39
definition language so drop command is
3:42
used to remove the complete structure of
3:44
the database and truncate is used to
3:46
remove all the rows of a database but
3:48
here the delete command is used to
3:50
delete a specific rows based upon the
3:52
condition
3:53
so the syntax for delete
3:56
the syntax for delete is
4:00
delete
4:02
from
4:06
table name
4:10
where
4:12
condition so here we can write the
4:14
condition so from this condition based
4:16
upon this condition the resultant rows
4:19
will be get deleted
4:21
or this is one sentence and the second
4:23
syntax
4:25
is a delete
4:27
from
4:29
table name
4:32
so
4:33
this syntax will delete all the rows
4:36
from the table similar to our truncate
4:38
function okay so
4:40
this one
4:43
deletes
4:46
specific rows
4:48
or records
4:49
and this function
4:52
deletes
4:55
all the records all the records are all
4:58
the rules
4:59
from the table
5:01
right so this delete command is a dml
5:04
command and update is also dml command
5:07
update is to
5:08
reassign the values okay change the
5:10
value of an attribute and delete is to
5:14
delete the required rows
5:16
right so hope you understood this syntax
5:19
of update and delete commands in sql now
5:22
let's move on to the mysql and i will
5:24
execute the two statements okay two
5:27
statements are two commands that is
5:29
update and delete so that if you still
5:31
have any uh doubts those notes will be
5:34
get clarified so let's move on to the
5:35
mysql
5:38
hello friends
5:39
so just now we have seen the syntax for
5:41
two different commands in our sql that
5:43
is update and delete which are dml
5:46
commands
5:47
so so these are the syntaxes we have
5:49
seen just now so let us execute these
5:52
queries
5:53
in mysql so let me open the mysql
5:56
so give the password and let us clear
5:59
the screen first
6:00
yes so we have created one database to
6:03
execute the queries that is a
6:04
youtube database let us move on to the
6:06
youtube database and here we have
6:09
created one table
6:10
uh that is a student so there are some
6:12
sort of records in student table so let
6:14
us check for that
6:15
those records select star from student
6:17
will get the complete details of the
6:19
student details so there are some seven
6:21
records seven sp records of a student
6:24
now we'll update
6:26
updated query or update command is used
6:29
to change the value of an
6:32
attribute right so it can be anything
6:35
but the primary key cannot be updated we
6:38
can't update the primary key value right
6:40
so only other than the primary key we
6:42
can update any attribute
6:44
for example if you just want to change
6:47
the percentage of
6:49
student sandeep
6:51
what's the syntax so give that one
6:53
update stable name update sandeep
6:56
set the column name percentage is equal
6:59
to sum 95
7:01
okay condition where yes name is equal
7:04
to
7:05
let us take sunday
7:08
execute
7:13
sorry here we have given table also send
7:16
it right table student no so student
7:19
set
7:20
percentage is equal to 95
7:24
where
7:25
yes name
7:27
is equal to
7:29
so first this condition will be executed
7:31
and it will get all the rows whose name
7:34
is sandeep and those percentages will be
7:36
get updated with the 95 so previously it
7:39
was 99 so once you executed this one so
7:43
you can see select star from students
7:45
now it was 95 so that means we can
7:47
modify the attribute value by using the
7:51
update
7:52
not only the single value so here it is
7:54
a single single value now i just want to
7:56
update multiple values
7:59
okay in order to update the multiple
8:00
values how we can
8:02
so for example for that let me
8:04
uh
8:05
add one more column here so alt in order
8:08
to add one more column
8:10
alter
8:12
table student
8:16
and
8:17
i'll give a grade with a var care of
8:21
some 40
8:24
execute one row is affected right so
8:26
select a star from student you can
8:29
observe here
8:30
so we had a grade column initially every
8:34
value of this grade is null now we need
8:36
to update this one so how can we update
8:39
we have to give a grade
8:41
such that
8:42
the 40 is a pass
8:44
and in between 40 to 60
8:48
okay in between 40 to 60 it's pass and
8:51
60 to 70 it's a first class and above 70
8:56
first class with extinction right so we
8:59
need to update this one so for that we
9:01
can see update student
9:04
student
9:05
set
9:07
grade is equal to
9:09
pass
9:10
give the condition here where percentage
9:13
is greater than
9:19
35
9:20
and percentage
9:23
less than 60
9:26
okay percentage less than 60 simply
9:28
update here you can get the details see
9:31
the student who got the percentage in
9:34
between 35 and 60
9:37
so we'll get a pass right now again
9:39
change the data
9:41
see
9:45
now
9:47
change the grade to first class i'll
9:49
give the first class
9:51
first class
9:52
where percentage is greater than
9:57
60
9:58
and percentage less than
10:02
70.
10:04
so
10:05
two rows matched you can observe here
10:07
what are those two rows so 68 percent
10:10
which is the first layer 65 percent
10:11
which is the first class
10:13
now again you can update the thing so
10:17
above 70 those are nothing but
10:19
restrictions
10:22
distinctions
10:24
distinction right where above
10:26
we can simply use the 70.
10:32
execute
10:33
okay and here you need
10:36
see
10:37
95 percent
10:38
destination
10:40
i mean sorry the 95 percent the grade is
10:42
distinction and 85 percent which is
10:44
great is distinction 68 first class 77
10:46
distinction so we can update is either
10:49
single row or a multiple rows so based
10:52
upon the result from the condition
10:55
so the number of rows will be get
10:57
affected with the given
10:59
value so that is the
11:01
update function that's simply update
11:04
function similarly
11:06
delete function delete function
11:08
so here
11:10
delete function the syntax let us see
11:11
the ceiling text so there are two
11:13
functions okay two queries so delete
11:16
from table name where condition and
11:18
delete from table so we can delete a
11:20
single row or a multiple rows or all the
11:22
rows so in order to delete a single row
11:25
or a multiple row we can use this delete
11:27
option delete from table name where
11:28
condition
11:30
so
11:31
you can write here delete
11:33
from
11:34
student where
11:36
where
11:37
grade is equal to pass
11:41
so one row is affected you can see
11:43
whatever the student 105 the student
11:45
details of 105 who got the grade pass
11:48
will be get deleted here
11:51
see 105 has been deleted because we have
11:54
we are deleting the student details
11:57
whose grade is equal to pass now
11:59
let us check here
12:04
so i'll delete the first class
12:06
i will delete the student details whose
12:09
grade is the first class now you can
12:11
observe two rows affected and see only
12:14
the distinction will be remained here
12:16
because in our table we are having the
12:19
first class two first class and one pass
12:21
in among the seven rows so we have
12:23
deleted pass
12:25
and first class so three rows has been
12:27
deleted so totally four rows has
12:29
remaining
12:30
whose grade will be the only distinction
12:32
only the
12:33
distinction so this is how we can delete
12:36
either single row or a multiple rows
12:40
either single row or multiple rows and
12:42
if you want to delete all the all the
12:44
rows simply you can use a command
12:47
delete from
12:49
student which will delete all the rows
12:52
you can see we will get empty set
12:55
we'll get an empty set
12:57
right so this is the option
13:00
these are the dml commands two commands
13:03
update and delete so which will use it
13:06
to update the value or delete the
13:09
records
13:10
update the value or delete the records
13:12
update can be done for single row or
13:14
multiple rows and delete can be done for
13:16
a single row or multiple rows or all
13:18
that all the rows right so hope you
13:21
understood these two commands dml
13:23
commands so let's stop here and if you
13:25
are having any doubts regarding these
13:27
two queries so feel free to post your
13:29
doubts in the comment section definitely
13:30
i will try to clarify all your thoughts
13:32
if you really enjoyed my session like my
13:34
session share my session with your
13:36
friends
13:37
and don't forget to subscribe to our
13:39
channel thanks for watching thank you
13:42
very much