7 hours 36 minutes
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.
Those three tables include that be account table,
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.
So we're gonna pretend that we have a requirement to switch the full name
in users with the account name and be account and the full name
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,
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.
For example, we go and look at the B account data. We will see
that we have
subsidiary Texas, California and then we have employee names like Andrew, Michael and Baker Maxwell.
I want to make sure we stick with the employees
and not accidentally switched something else. We're gonna tie in the employee table and ride that to the user table.
So if we look at the B account data, we'll see that
the names have prefects is like Mr Ms and Ms
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.
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,
while the full name in the user's table is going to become the value of
the account name. And we're gonna set this quarry up in a way that we can just swap them back and forth
so we can change them, and then we can change them right back.
Sorry about that. So let's go ahead and start writing the query for this.
So we're going to need a new sequel editor
and an update statement starts with the table. So we're gonna say, updates
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.
So we're going to the E P employees,
and we're gonna need
never going to set
We want that equal to the e p A employees
account. Let's check that name again, just to be sure we get it right. Account name.
so it would not account Name?
Uh, wrong table Won't do that to be a count,
which stands for business account.
Town name. So we've set
in this line. We have set the user's full name to the B account account name,
but we need to do a swap, so we need one more.
It's arrested to be account
count, name again. Equal to the users,
not full name.
And now we're gonna ply our wear conditions to make sure that we
do. We perform this action against the correct accounts. So
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.
Why are the user I d? Okay, so users that p k I. D.
Is equal to
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
is equal to E P
That company idea
k k i. D.
Is equal to the e p a employees
dot user i. D.
And mmm, the user's that
company I d.
Is equal to the
that company i d.
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
employee company as well. And I'll just go ahead and switch it
because it will behave the same way
finish out the relationship. We need
the account idea, the business account idea.
You know that.
so this is a little bit more of a complicated update, but let's go ahead and walk through it. So
in our update statement, we've listed the tables that we will be using.
And even though it says update table names,
the only tables were actually updating our the users
and the B account table.
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.
In the next statement where we set, we're we're we're doing we're performing the swap.
So we're saying the user's that full name will become the B account, that account name
and the B account. That account name
will become the user's that full name. So they're swapping.
And in the next statement, we have set up our conditions. Which follows this diagram.
No, I left the company idea out of this diagram, but the company idea crossed
All three of these tables will be the same, which is set to two,
as that is the primary company I d.
Now, if there is another company in there three we will perform that same switch in this quarry. Unless we do this
where we say, OK, we want to make sure that this happens. But we only want affect company too,
which, you know, could very well be a, uh a metric that you have to include.
So we're going to
relate these tables together from uses to e p employee by the
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
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
and that will perform the switch for us. So let's go ahead and
Okay, so we updated 90 Rose.
Let's go to be account and see what happened. We should see these prefixes drop off
and sure enough, they're gone.
And in the user's table, we should see prefixes pop on.
And sure enough, they did.
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.
So we'll click the play button again.
90 updated records.
And if we go back to the B account table and click refresh
again. The prefixes are back on.
We go to the user's table, look at the full name
and we will see the prefixes fall off.
And sure enough, they did. So we reverted back to what we had.
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
from the user's table and the B account table vise versa.
And we wrote the QUERIAN away or the update statement in the way that it would reverse itself.
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,
uh, again, I hope you got this working. And that completes this lesson. I will see you in the next lesson. Thank you.
Penetration Testing and Ethical Hacking
To assess the strength of your organization’s cybersecurity posture, you need to gather information, perform ...
7 CEU/CPE Hours Available
Certificate of Completion Offered
Ready to dive into SQL queries, aggregations, events, and database schemas? In our Intermediate SQL ...
10 CEU/CPE Hours Available
Certificate of Completion Offered