So next step we move on to the you in crud, which is update.
How do we update existing rows in a table?
This is something we do pretty frequently.
We certainly insert and select more more often than updating, but it still is really common to need
to change data that's already in a table.
Whether we have some website and restoring user information, a user changes their password or they
change their username, or we alter some existing order, something in a shopping cart item to get added
or removed.
Things are dynamic.
We update information.
So how do we update?
Instead of SQL, we use an update query.
So update and then the table and then set.
So they kind of go together.
Well, they don't kind of they go together in the same way that we saw insert into and then values.
That's a pair.
Update.
And then some table set is a pair.
So in this case, we have to say two things.
What are we trying to update?
We're changing breed to be short hair.
And then on which rows are we performing that update only where Breed is set to Tabby.
So if we didn't include this, where let me just show you on a different table.
I don't want to mess with our cat's table because we have an exercise coming up, but I think we have
employees.
If I did select staff from employees.
Okay, if I did something like update and then employees set and then let set last name to be Rooster
Beard just like that.
If I had enter here, what we just did was update every single row in the table employees to have a
last name of Rooster Beard.
Now, in our case, that was three, but if there was 10,000 employees or 100,000 employees, they all
would have a new last name of Rooster Beard.
So if we don't specify the where it's totally valid, we don't get an error, but we probably don't
want to do that most of the time.
We usually want to be more precise with our updates.
But if you ever do need to change every row on a table, you would use the syntax, update the table
set, whatever you want to set and you don't have a where at the end.
Now we can also update multiple fields at once.
So let's say I want to set I don't know, we're doing very sad mass layoff.
We're updating everybody to have current status of, I don't know, fired or laid off.
And we're going to set everybody's last name to be Who cares?
Very sad.
We've laid everyone off.
We don't care about their name and their status is going to be laid off.
It's just a stupid example to show you that we can update multiple columns at once.
So we say the table employees and then we're going to set current status to be whatever we want, let's
say, fired or laid off.
And then with a comma, I can also say last name equals.
Who cares?
Okay.
It's kind of a cruel update, but let's do it.
And if I select star from employees.
We see that both of those columns were changed in this case for every single row.
So we don't have to update one piece of a row at a time, one column.
We can update as many as we want.
But I think you'll find you tend to not update a whole bunch of different columns all in one go, but
you can it's just maybe less common in the real world.
So as I said, you don't have to have the where, but we often will have a way or clause at the end
to target what we're updating.
So this case is saying find all of the cats that have breed of tabby and set breed to be short hair.
Or here's another example.
We're going to update all the cats in this table that have name of Misty to have age of 14.
So let me show you that one.
Let's go back to our cats Select star from cats.
So here's Misty.
I can select her, although I'm not going to use select I'm going to use where along with update.
So update cats and I want to set her age to be 14 because she had a birthday.
It's 13 right now.
I'm going to set it to be 14.
Again, if I just hit enter now, every single cat would have age of 14, but I'm going to narrow it
down and say where name is equal to Misty.
And if I do a select star from Cats, we see Misty is now 14 years old.
So that's the basic concept of how we update.
We use update some table name like cats, and then we set some values so we can say column equals some
vowel.
Another call equals some other vowel.
And then often we narrow, we clarify where we're trying to update using the where clause.
So only where name is equal to chicken or age is equal to 12, whatever we want.
That's the basic pattern update with set and then usually aware at the end.