Hey guys welcome back to this SQL tutorial course. In this session we will
learn how to update and delete records in another table using the join
condition on two tables. So let's understand how can we use update with join
here we have two tables the department table and the employee table
now I'd want to update the age value in the employee table wherever the
department location is New York. So from the department table we can find out
that content and analytics departments are based in New York so in the employee
table we'll update those age values where the department is either analytics or
content and after updating the table using the join statement we see that
these two values have been modified so we have the department and the employee
tables right in front of us and we'll be implementing the same task. So from this
employee table we'll add ten more to the age values wherever the department
location is equal to New York and since we've already seen that content and
analytics are based in New York so basically these two records where the
e_dept value is equal to analytics we will be adding ten more to these two age values. So
let me write the syntax for that. I'll type update and then give the name of
the table which I want to update so that would be employee after that I'll use
the set keyword and then update the age value so e_age and I'm adding ten more
to the e_age value. After that I'll use the from keyword and give the name of the
first table which would be employee then I'll type join and then give the name of
the second table which would be Department then I will type on and then
stay the join condition. So the join condition is the values of the e_dept
column from the employee table should be equal to the values of the name column
from the department table. So employee dot e_dept should be equal
to Department dot d name and finally we'll specify the where condition which
would be d_location should be equal to New York
so I'll select all of this. I ll click on execute so you see that two rows have
been affected now let me have a glance at the modified employee table select
start from I'll type the name of the table which would be employee now I'll
execute this. So let me see the original age values so over here the department
is analytics and we see that the initial age of an Ann and Julia is 25 and 30 and
after applying the update command we see that the age has been modified to 35 and
40 so Ann initial age was 25 after updating it became 35
similarly Julia's initial age was 30 after updating it became 40 so this is
how we can use update with the join condition. Now let's understand how can
we use delete with join. So this time from the employee table I'll be deleting
those records where the department location is equal to New York and since
content and analytics are based in New York we will go ahead and delete those two
records from the employee table so in the final modified employee table we see
that there are no records for the department is either content or
analytics. So again we'll be implementing the same task from this employee table
I'll be deleting those records where the department location is in New York. So
let me write the syntax for that so I'll type delete and then give the name of
the table from which I'd want to delete the records so that would be employee
after that I will give the from keyword and then give the name of the first
table which would be employee again then I'll type join and then give the name of
the second table which would be Department. Now I will give on and give
the join condition which is same again so the values of e_dept column from the
employee table should be equal to the values of D name column from the Dept
table. So employee dot e_dept should be equal to Department dot T name and
finally I'll use the where condition which would be D location should be
equal to New York
so I'll select all of this I'll click on execute so again we see that two rows
have been affected. Now let me have a glance at the modified table, select start
from employee
so see that there are no records for the department is analytics so if you have a
look at the original table we see that there are two records where
the employees were present in the analytics department and after deleting
those two records we just have four records present in the employee table
and this is how we can work with update and delete statements using join. Thanks
for attending the session. Let's meet in the next class.