3.8 Complex Update

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,