Instructor: Hi guys and welcome back.
In this video we're going to learn about the UPDATE query.
UPDATE looks more or less like this.
Here we've got a UPDATE query that does UPDATE users,
so it's going to change the users table
and it's going to set the surname to Wick
where the surname is currently Smith.
So here's how that's deconstructed.
UPDATE users tells the database
that we want to update rows in this user's table,
then we must tell it the change we want to make to each row.
So we're doing SET surname equal 'Wick'.
So we're going to change the surname column
of every row in the table to Wick.
Naturally, normally you don't want to change
every row in a table to the same value,
so that's where the WHERE clause comes in.
Filter them so only certain rows gets updated.
Remember the WHERE, it's very important.
Otherwise all the rows in your table
are going to get updated.
And if you do make a mistake like that,
don't commit to the transaction
and don't save the SQLite database,
just roll back and you'll be fine,
otherwise you're going to have to recreate your data.
You can update multiple columns as well.
You just separate them by commas, and it's no different,
just set a value for each kind of like this.
Here we've got UPDATE users and then the SET part,
first name equal John, comma, surname equal Wick.
So that's gonna change both columns
first name and surname to John Wick,
and the columns that are gonna change
are the ones that have a current surname of Smith.
So notice that in the filter in the workloads
we're not filtering by surname.
So notice that in the WHERE clause,
we're not filtering by first name,
we're only filtering by surname, but that's okay,
we're gonna change the first name and the surname.
You can see here that in the SET part of this query
we're using the equal sign,
and in the WHERE clause we're also using the equal sign.
And so wasn't the equal sign a comparison operator?
Wasn't it used to compare two values
like we do in the WHERE clause?
Well, we did see that,
but actually it's the same in the SET part of the query.
We use the equal sign to assign essentially
to the column their new value.
So that's just an overloaded operator,
an operator that has two meanings
depending on where it's used.
I don't like this either, but that's just how it is.
In Python we've got equal equal for comparison.
In SQL we just use one symbol to mean both things.
Obviously it's clear that you can't use
comparison operators in the SET part.
So don't do this,
don't do SET first name not equal to John
or set salary less than 35,000,
it doesn't make any sense
and you're going to get an error as well.
All right, so we're back here in the code editor.
We're going to add our UPDATE query to our application
and complete the function that was missing.
So the first thing to do is to
create our SET movie watched query,
and this is going to be UPDATE movies
SET watched equal one, where the title
is equal to question mark.
So this is gonna be the argument.
Now that we've got this,
we can go down to watch movie
and simply do with connection,
connection.execute,
SET movie watched,
and passing the title as the argument.
Again, that is gonna pass in that to here
replacing the question mark,
so we're going to run the UPDATE,
setting the watched column to have a value of one
for those rows where the title equals
what we passed in.
Pretty straight forward.
Now that you know how the UPDATE works,
we are able to set movies to watched.
Thank you for watching,
I'll see you in the next video.