SQL UPDATE Statement |¦| SQL Tutorial |¦| SQL for Beginners

SQL UPDATE Statement |¦| SQL Tutorial |¦| SQL for Beginners

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.

31 thoughts on “SQL UPDATE Statement |¦| SQL Tutorial |¦| SQL for Beginners

  1. Suggestion for more advanced topic. When should you use "select for update" and when are "dirty reads" ok. As a follow up advanced topic, difference between MVCC versus Spin lock design on multiple updates.

  2. wayyyy easier and more exciting to learn than alot of these other channels thank you. Not to down talk on the other channels

  3. Such good tutorials short easy to understand. I told the Commision, "Socratica, as usual, has done an exemplary job on the SQL UPDATE project. Please do your best to extend her audience."

  4. Especially for production databases:
    1) please wrap updates in a transaction
    2) please write the where clause first and then add the set statement above it.
    Why – we all make mistakes!

Leave a Reply

Your email address will not be published. Required fields are marked *