Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
It's more useful to update specific rows. Like with SELECT statements, you can use conditions to target specific rows.
SQL Used
An update statement for specific rows:
UPDATE <table> SET <column> = <value> WHERE <condition>;
Examples:
UPDATE users SET password = "thisisabadidea" WHERE id = 3;
UPDATE blog_posts SET view_count = 1923 WHERE title = "SQL is Awesome";
Update multiple columns for specific rows:
UPDATE <table> SET <column 1> = <value 1>, <column 2> = <value 2> WHERE <condition>;
Examples:
UPDATE users SET entry_url = "/home", last_login = "2016-01-05" WHERE id = 329;
UPDATE products SET status = "SOLD OUT", availability = "In 1 Week" WHERE stock_count = 0;
See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Now it's one thing changing
all entries in a table,
0:00
how about targeting specific rows?
0:03
I can see that being more useful.
0:06
Let's start with our original UPDATE
statement that updates all the rows.
0:09
How do you think we should select
the rows where we want to update them?
0:13
Well, just like a SELECT statement
where you can use a WHERE clause,
0:17
you can do the same thing with WHERE
conditions on UPDATE statements.
0:21
This time, instead of reading the rows,
0:26
you're updating the rows
that match this criteria.
0:28
Remember, conditions come
in all shapes and sizes.
0:32
Some have operators such as a quality and
inequality, greater than or
0:36
less than to name a few.
0:40
Then there's searching in sets
of values with the IN keyword.
0:42
There's also conditions
where you can search for
0:46
ranges with the BETWEEN keyword.
0:48
Finally, you could use the LIKE
keyword where you could search for
0:51
patterns of characters.
0:55
If you've taken the prerequisites or
have the prerequisite knowledge for
0:57
this course, you should be
familiar with these conditions.
1:01
Let's use some of these
conditions to update rows.
1:04
Let's look for some missing
information in our books table.
1:08
As you can see in the last entry,
1:14
Animal Farm, there’s a missing
value in the genre column.
1:16
What condition would we use in
order just to select this row?
1:20
The most unique value
would be the ID of 20.
1:24
Other books could have the name,
Animal Farm, or the same author,
1:37
or released in 1945.
1:42
Let's update the genre of
Animal Farm to Classic.
1:44
All right, let's take another
look at Animal Farm again.
1:58
It's now a Classic.
2:13
Let's update our loans table too.
2:15
Let's take a look at it and
see what values we're missing.
2:18
Let's say the patron
with the ID of 1 returned
2:22
all their books that they've loaned,
books 4 and 8.
2:25
Let's imagine today is December 18th,
2015.
2:29
So let's start with UPDATE loans
2:35
SET returned_on =
2:40
"2015-12-18".
2:44
Now to the WHERE clause.
2:50
Well I could do something like this,
WHERE patron_id = 1,
2:54
and this will update the rows we need.
3:00
But imagine there were hundreds or
thousands of rows,
3:04
we may not want to update
all the loans for patron 1.
3:08
They may have loaned hundreds
of books over the years, so
3:12
to qualify it further would be better.
3:16
I could do, AND,
3:19
returned_on IS NULL.
3:27
This will only update loans
that hadn't been returned for
3:34
patron 1, and not any historical ones.
3:37
Now you can go even further and
3:40
add, AND book_id IN (4, 8).
3:47
Just in case if they had rented
out a different book and
3:54
they hadn't returned that one yet.
3:58
But since we can see all
entries in the loans table and
4:00
all the entries for patron 1,
we can simply do, WHERE patron id = 1
4:05
And as you can see
the returned_on has been updated.
4:15
Depending on your circumstances,
4:19
you can update multiple rows based
on any condition you construct.
4:21
The WHERE conditions are exactly how
you'd use them in a SELECT statement.
4:25
Instead of retrieving rows,
you're updating them.
4:30
So before running the UPDATE statement,
you can try your condition out first with
4:33
a SELECT query to make sure that the rows
returned are the ones you want to update.
4:37
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up