Please hold on for just a second.
I’m getting an update from headquarters.
Yes?
Of course.
Really?
Oh, stop it…
Flattery will get you everywhere…
I am hanging up now…
Thank you for your patience.
We have orders to make changes to the data in our database.
Fortunately, SQL has a command for such occasions: the UPDATE statement.
Today, we will learn how to use it and to use it carefully.
Ok, I’m back.
You were saying?
In this video we will make changes to a top secret user database.
I am not permitted to tell you the purpose of this data, because it requires a Level
9 security clearance.
I can, however, show you the schema…
Our ‘secret_user’ table has the following columns:
user_id first_name
last_name code_name
country organization
salary in US dollars, and knows_kung_fu
Let us first see how many rows are in this table.
Not that many.
It must be an impressive list.
Aww heck, no one is watching.
I’ll go ahead and show you the data.
What’s the worst thing that can happen??
Here we have an impressive list of people in the secret_user table.
Wait a sec - it looks like we have an incoming message.
Apparently Mr. Bond does not like everyone calling him Jimmy.
Let us change his first name to ‘James.’
To do this, enter “UPDATE” followed by the table name.
Next, we write a SET clause.
Here you specify the column name followed by the new value.
And then finally - and this is very important - we specify the ROWS to make this change.
We only want to update the name for “Jimmy Bond”, who has a “user id” of 1.
So we can make sure this update only applies to Jimmy B by writing a WHERE clause, which
says to only make the change for user 1.
Execute.
If we select the data, we see that the change has been made.
Hopefully 00-7 will calm down.
It was only a joke.
What’s this?
Another incoming message.
Jack Ryan has a complaint.
He’s the only one without a code_name.
He wants to be known as “Neo 2.0” Also, he is currently receiving the salary
of an analyst, even though he spends most of his time in the field.
Jack is a valuable asset, so to keep him happy we will make TWO changes to his data.
Like before, write UPDATE and then the table name.
Next, write SET with the value for the code name… a comma, then the new salary.
We’ll make sure he’s paid the same as Jason Bourne.
Notice that you can update more than one value in the SET clause.
Finally, the WHERE clause.
We could use the user_id once more, but for funsies let us specify that these changes
should only apply for the person with first name “Jack” and last name “Ryan.”
Execute…
Confirm…
And relax...
Now that we’ve seen a few basic examples, let us look at the general form of an UPDATE
query.
The first line is UPDATE and the table name.
Next, you write a SET clause with a list of columns and new values.
You can update one or more columns in a single query.
If you update more than one column in a query, separate the changes with commas.
And finally, the WHERE clause.
This is essential to make sure you update the correct rows.
If you do not include a WHERE clause, the change will apply to every row in the table.
I repeat: if you do not include a WHERE clause, the change will apply to every row in the
table.
While we wait for our next set of orders, I would like to tell you a joke.
Oracle, Postgres, and SQLite walk into a bar.
Oracle walks up to the bartender and says, “I would like a drink!”
The bartender says in a gruff voice, “Lookey here old man…”
That’s my cue!
Back to it!
Another message.
Apparently the agents at MI6 are not amused at being paid less than their CIA counterparts.
We can appease Jimmy, George, and Emma by updating their salaries to 115,000 per annum.
This can be done in a single UPDATE query.
The secret lies in the WHERE clause.
We will update secret_user…
And set the salary to 115,000 And our WHERE clause will ensure this only
applies to agents in the MI6 organization.
Execute…
Trust, but verify...
One thing that concerns me is how few agents know Kung Fu.
I think it time that we send a message of our own.
Well that was fast.
Apparently Ethan Hunt, Susan Hilton, and Nick Fury have completed a one-day Kung Fu intensive
at the Beverly Hills Martial Arts Studio and Day Spa.
I should not be surprised.
With their salaries they can afford it.
Let us update their profiles to show they now believe themselves to be kung fu experts.
We UPDATE secret_user Set knows_kung_fu to TRUE.
Now for the WHERE clause.
We want this update to only apply for users 5, 7, and 8.
To do this, we will use the IN operator.
This clause will only update records where the user_id is in this list.
Execute.
And certify…
I hope they know what they’ve signed up for.
They could be fighting forest Ninjas any day now.
What’s this?
An inflation alert?
Apparently everyone will get a 10% raise.
Fortunately we can do calculations in an UPDATE query.
We set the new salary to be 10% more than the old salary.
Since everyone is getting a raise, we do not need a WHERE clause.
Remember, if you do not include a WHERE clause, the change will apply to every row in the
table.
Every… single… row...
Execute.
Confidently check that our query was successful.
And it was.
The salaries have been raised...
We can compute the combined salaries for these agents with a SELECT query.
We use the SUM function to add up all the values in the “salary” column.
Execute.
1,457,500 dollars.
If you think this is high, you should see the insurance costs for these characters...
They’re back, and they are all looking at me.
Ask them to support us on Patreon?
They know how important it is to us…
I’m sure they’ve visited our Patreon page, did the math, and realized we need more help
You can get Jimmy Bond to do that!
He loves the camera.