Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I Welcome back the Module three sequel programming. This is lesson to queries and sub lesson 2.3 a complex updates statement. And in this lesson, we're gonna look at a update statement that requires three tables to complete.
00:15
Those three tables include that be account table,
00:18
the E P A employees table and the user's table. And I've diagram that out in the previous application that we worked on d b designer dot net to show you the relationship between these three tables.
00:30
So we're gonna pretend that we have a requirement to switch the full name
00:34
in users with the account name and be account and the full name
00:38
and users.
00:40
We're gonna place that in to the B account. So we're gonna do a swap account name is gonna become full name, and full name is gonna become the account name value. And to do that,
00:50
we're gonna have to use these three tables to facilitate that. And that's because B account which sense for business account includes more than just employees in it.
01:00
For example, we go and look at the B account data. We will see
01:03
that we have
01:06
subsidiary Texas, California and then we have employee names like Andrew, Michael and Baker Maxwell.
01:12
I want to make sure we stick with the employees
01:17
and not accidentally switched something else. We're gonna tie in the employee table and ride that to the user table.
01:25
So if we look at the B account data, we'll see that
01:27
the names have prefects is like Mr Ms and Ms
01:33
attached to them. Whereas if we look at the user's table, we will see that in the full name that that it's missing.
01:40
And in this assignment, we're going to a swap. So the account name is gonna become four employees, is going to become the full name of the user's table,
01:49
while the full name in the user's table is going to become the value of
01:55
the account name. And we're gonna set this quarry up in a way that we can just swap them back and forth
02:00
so we can change them, and then we can change them right back.
02:07
Sorry about that. So let's go ahead and start writing the query for this.
02:12
So we're going to need a new sequel editor
02:15
and an update statement starts with the table. So we're gonna say, updates
02:21
users.
02:22
We're gonna list the tables that we need. Now, within this list, there's gonna be one table that we're not actually updating or just using it as part of the relationship.
02:30
So we're going to the E P employees,
02:34
and we're gonna need
02:38
be account
02:40
never going to set
02:43
users that
02:45
full name.
02:46
We want that equal to the e p A employees
02:52
dot
02:53
account. Let's check that name again, just to be sure we get it right. Account name.
02:59
Okay,
03:00
so it would not account Name?
03:07
Uh, wrong table Won't do that to be a count,
03:13
which stands for business account.
03:15
Town name. So we've set
03:15
in this line. We have set the user's full name to the B account account name,
03:22
but we need to do a swap, so we need one more.
03:25
It's arrested to be account
03:29
count, name again. Equal to the users,
03:35
not full name.
03:38
And now we're gonna ply our wear conditions to make sure that we
03:43
do. We perform this action against the correct accounts. So
03:50
where
03:52
users
03:53
Let's take a look at our diagram again to see how that connects. So we're gonna go from users to the e. P a employees table.
04:00
Why are the user I d? Okay, so users that p k I. D.
04:05
Is equal to
04:10
actually let's stay in the same company. I didn't put that in the diagram, but let's go ahead and make sure we stay in the same company
04:15
is equal to E P
04:18
employees.
04:21
That company idea
04:24
and
04:28
users dot
04:30
k k i. D.
04:31
Is equal to the e p a employees
04:38
dot user i. D.
04:41
And mmm, the user's that
04:46
company I d.
04:49
Is equal to the
04:53
the account
04:54
that company i d.
04:57
Now I could have used the user's table. I did use the user's table table here, but I could have easily have just done the E p
05:03
employee company as well. And I'll just go ahead and switch it
05:08
because it will behave the same way
05:15
and
05:16
finish out the relationship. We need
05:20
the
05:23
the account idea, the business account idea.
05:27
You know that.
05:29
Okay,
05:30
so this is a little bit more of a complicated update, but let's go ahead and walk through it. So
05:36
in our update statement, we've listed the tables that we will be using.
05:41
And even though it says update table names,
05:43
the only tables were actually updating our the users
05:47
and the B account table.
05:49
The E p A employees is serving as a bridge between those two tables so that we can carry the relationship across that table structure and hit the targets that we want to hit.
06:01
In the next statement where we set, we're we're we're doing we're performing the swap.
06:08
So we're saying the user's that full name will become the B account, that account name
06:13
and the B account. That account name
06:15
will become the user's that full name. So they're swapping.
06:18
And in the next statement, we have set up our conditions. Which follows this diagram.
06:24
No, I left the company idea out of this diagram, but the company idea crossed
06:29
All three of these tables will be the same, which is set to two,
06:35
as that is the primary company I d.
06:39
Now, if there is another company in there three we will perform that same switch in this quarry. Unless we do this
06:47
where we say, OK, we want to make sure that this happens. But we only want affect company too,
06:54
which, you know, could very well be a, uh a metric that you have to include.
07:00
So we're going to
07:03
relate these tables together from uses to e p employee by the
07:09
primary key i d. For the user's table with the user i d and the E P. A. Employees table is gonna make sure that we're talking about employees that air users
07:16
on them from that table. We're making sure that we have the B account i d from the E p a employees table related to the B account i d. In the actual be account table
07:27
and that will perform the switch for us. So let's go ahead and
07:31
run this.
07:33
Okay, so we updated 90 Rose.
07:35
Let's go to be account and see what happened. We should see these prefixes drop off
07:43
and sure enough, they're gone.
07:45
And in the user's table, we should see prefixes pop on.
07:49
And sure enough, they did.
07:54
Now let's say, Oh, we don't want to do that But we actually wrote this update in a great way so that we could just run it again and it will reverse itself.
08:03
So we'll click the play button again.
08:07
90 updated records.
08:09
And if we go back to the B account table and click refresh
08:13
again. The prefixes are back on.
08:16
We go to the user's table, look at the full name
08:18
and we will see the prefixes fall off.
08:22
And sure enough, they did. So we reverted back to what we had.
08:28
And now
08:28
that pretty much completes his lesson again. What we did is we took a look at a more complicated update statement that involved three tables. We have the simple requirement of exchanging the full name with the account name
08:41
from the user's table and the B account table vise versa.
08:45
And we wrote the QUERIAN away or the update statement in the way that it would reverse itself.
08:50
So that completes this lesson. I hope you got your update. State statement working and feel free to, you know, practice running different types of updates. Statements against the database as practice is the best way to gain experience. And to really understand what is going on,
09:05
uh, again, I hope you got this working. And that completes this lesson. I will see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introduction SQL training course teaches SQL core concepts that are applied in professional environments. Once completed with the course, students will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor