Is that cost money?

If you appreciate this education, please think about going to Eli the computer guy dot com and offering a one time or monthly recurring donation.

Welcome back.

As you know I am.

He lied to Computer Guy, and in today's class, I'm going to be showing you how to up today and delete records within tables within your my sequel database.

So at this point, you should know how to be create databases.

You should know how to create tables.

You should know how to in a certain records into tables and how to use the select statement to be about pole records out of tables.

So at this point, we're going to show you how to basically modify those records.

So in the previous examples, and basically I put a number of kids as records into the user's table that we created.

And so when the questions is, what happens if one of those kids get older?

You know, they went for me nine years old, and now they're 10 years old, so you need to go in there and you'd actually be able to modify that record So that is what I'm going to show you today.

It is basically using what is called the update commands.

You'll do update on.

You'll be able to update records within your tables within my Siegel.

Then, of course, you know what happens if a user leaves, or for whatever reason, you want to delete a record within your my sequel table, I will be showing you how to use the police statement in order to do that.

And so that is what we're going to be learning today.

So now that we're ready to learn how to be ableto update records and delete records within your my sequel database tables, let's go over to the computer so I can show you how this works.

So here we are, back at my lab environment again, I'm running a Mac book professional using virtual box within virtual box.

I have spun up an instance of a bunch of desktop 18.0 force that's going to give us a graphical user environment, and this is what I have installed my sequel onto.

So if you have my sequel installing on Tween normal machine, that should work fine for you.

I guess to explain what's going on here.

You go down here to show applications, then we're gonna type in terminal because we need to get that command prompt when you the terminal.

And now we are in the terminal session so we can actually get into my sequel.

Now, if your brand new to this series of classes you have not watched the previous series of classes.

I don't log in to my sequel the normal way.

So the normal way of logging into my sequel is my sequel Space Life in You User name Space Hyphen piece.

So this is the normal way that you will be along in N to you're my secret database server.

We have not created user accounts yet dealt with privileges and all that.

I'm kind of teaching some way, some would say and have an ass backwards way, But I think it's an ass front.

Words away.

I don't know that came out wrong.

The way that I'm doing it right now is to do my sequel.

There are no do do pseudo pseudo my sequel.

So what this will do Is this Well, actually a log us in as the root a local host account, eh?

So this gives us all the full privileges on this particular database server.

If you already have a normal user account on, you're my secret database server with the appropriate privileges.

Deal with that.

I'm just doing it this way because that's how this particular series of classes is going.

And it's hit.

Enter super secret password.

123456 on You Hit Return and you were in my sequel.

Uh, so I thought I talked about the previous class that whenever you're doing thes these little labs that you should try to recreate your database, recreate your table's recreate your rose or your records for every class to get you used to actually doing that.

I'm not going to do that today for this class because I don't want to waste the time.

I'm simply going to be using the database and the table that we already created in the previous class so we could go here so show that base is a semi colon and we could go take a look at our databases.

The one that we're gonna be using here is class the D B.

That's not be our database we're gonna go use, uh, Class B.

Now we have changed into the class d be allowed to control l to clear the screen.

And then just to make sure that we are in the right database, will d'oh select that a base, this command open brothers, he's close parentheses, semi colon enter.

And so this just shows us that we are, in fact, in the correct data base.

This is important, remember?

Remember, in the my secret world, they don't give you warnings.

They don't ask you if you want to do something, you type a command, and it does the command.

So if you're in the wrong database, you can get bad really quick.

So always, always verify you're in the right past that.

Then what we're gonna do is going to show the tables to see what tables we have in here.

Okay, so we have a user's table.

We'll use the describe command.

DSC users could get an idea of what the user's table looks like.

So again.

So for the user's table, we've got to use your idea.

You got a name?

We got AIDS.

We have a gender user.

I d is an entity air and it is an auto increments.

That is our primary key name is text Ade is entered.

Your and gender is test.

So this is important again.

One of the things that's very important is if you're going to be updating records to know what data types you can use to be able to update.

Is it a text?

Is it afloat and again, like with interviewers?

Right End is a whole number 11.

25,000 and 25.

Those air enters floats or decimals would be 10.25.

Ah, 100.

Not 50 1150 not 25.

Right.

So if you have a decimal point, that would be a float.

So this is important, something very be thinking about.

Like if you have something like a JJ, if you try to type in, you know, 11 dot that 11.0.5 for an age, you might run into some problems.

So you do need to make sure you know what data types are dealing with here.

And then finally, just to get an idea of what the dad looks like with our database, would you select all from users?

So this is select all columns from users and we don't have anything else.

Nowhere statements.

Here s oh, it's going to take all the records and then we're going to hit inner and so we can see the user I d.

So number one is Bob, ages nine under his boy, Sue is he's ready to age 15.

Fred, this is righty 388 Gender boy on this is kind of interesting.

Here.

Just kind of see show you how this works.

So before I did the Chris class, I do a little Testing has make sure writing out all the statements properly.

And so I was the leading and adding some records.

And so one of the interesting things just to realize with this whole auto increments or user i d auto increments.

Every time a new record is created, it ticks up one place.

So you'll notice Pat.

Even though Pat is in the fourth position, the user i d is six because we've actually this point created six user ideas.

I've simply deleted a couple of them because I don't need them anymore.

But it is just kind of interesting to look at that from the auto increment standpoint to realize that Pat is not user.

I d four is actually user i d six and again when you're doing things like circuit is when you're doing things like updates, keeping that in mind could be could be important again.

Just because they're in the fourth position doesn't mean that's what, like their user i d or the part I d or whatever ISS s.

Oh, that's basically what we're looking at here.

Uh, so let's do the clearest.

Great.

Yeah.

So clear the screen, and then we just do the slept all from users.

So we're taking a look at this.

And so let's take a look at this thing up, OK?

So let's look at Bob.

Let's say bub has increased in eight by one year.

So Bob is now 10 years old.

Yeah, Bob is 10 years old.

So how are we going toe up a date, Bob?

So the way that we're going to update Bob is around to update, I'm gonna say the user's cable, so whatever the table is user stable and we're gonna set, um, So what we're gonna do?

Look, here is a JJ.

So we're going to say the column A.

We're gonna set 8 to 10 right?

And then we're going to say where, uh, for this gun, it's a user I d.

So use a righty.

Here is one equals one, and then we're gonna do a semi colon.

It's out.

Since these are numbers, since these air integers, I don't have to say to put single quotation marks around them.

I can simply put a number there, and then I will hit enter and we see a query.

Okay, One row affected, blase, blase, blase s so that if I go up, I do select all from users again.

We hit enter.

And so now we can see that Bob's age is now 10 years old, where it used to be nine years old in the past.

So that's all you have to do to be able to update a record.

So what you do is update the table, so update the user's table set Whatever column it ISS.

So we're doing a here, eh?

So we're gonna set it to be equal 10.

So the age will be 10.

Where and then, user, I d right.

That's a user, I d their equals one.

And then you do the same, Michael.

And then it's able to update Let me clear the screen.

And now again, let's do the select Oh, from users.

And now let's say I want to change a string.

Right.

So string is just where you have to use the single quotation marks, Eh?

So what we can do here is ah, update users, um, set, uh, said let's say name.

Um equals, Um uh, Billy Bob's.

Let's say Bob just can't.

His name's We now want to call him Billy Bob, and we're going to close the single quotation marks.

So again, when you're dealing with text, you want those single quotation marks and they were going to say where and again user underscore i d.

People's one there do semicolon there and then we're going to hit enter.

Hopefully everything's as query.

Okay, again, if you fat finger it and you put in anything in here wrong, it will work, right?

Uh, up So again, select all from users were going to hit in her.

And now we can see that Bob's name has been changed to Billy Bob.

So that is a basic idea of how to do update so again, when we're gonna be updating a number, so is an interviewer or afloat.

You don't have to use those single quotation marks.

Whereas if you were trying to update again a string, something like a name or a description or something like that, you will need to use the single quotation marks.

So with that, let's clear the screen again.

So now let's look at updating a record based off with a value of two columns.

Right?

So, uh, so one of problems you run into the my sequel world is as soon as you put an update.

As soon as you put a change through, it just goes through again.

You don't You don't get a question of Do you actually want to do this?

So imagine if you have multiple records that are similar, and so you're a little careful.

You're not sure if you wanna update based off of a single column alone.

Maybe you wanna update based off of a couple of different variables, right?

So we go up, we take a select all from users and we take a look and let's say we want to update sue.

But we're worried, right?

So we want to update Sue, but we're worried that there might be a couple of other shoes in this particular table.

So we say update, Sue, It will update all suits bit also So we don't wanna update all suits.

So what we're going to say is we want to update Soo, who also has a user idea of two.

And then we're going to change her age to 16 s.

What we can do is they do up date as we did before the user's table set a to equal with you now 15.

And so it's going to be 16.

Ah, where user i d equals two sues user I d equals two, um, and name equals Ah, single quotation Mark Su close single British mark because again, that is a string.

And then you're going to do the semi colon.

So basically it is on Lee going to update the age to 16.

If the user i d is too.

And the name is Sue so again, especially when you got large tables right now, I'm showing you a table of four records in it.

This may seem a little bit ridiculous, but imagine if it was 40,000 records.

So you really want to make sure If you're updating the record, you really want to make sure you're updating the right record on we got query.

Okay, so everything went through.

If you go up when we take a look at select all from users, we can see that Sue is now at age 16.

So that is where you can change of basically that one.

That one value based off of a long as two columns are correct so that let's do control, elegant, clear the screen.