Updating Specific Rows4:42 with Andrew Chalkley
It's more useful to update specific rows. Like with SELECT statements, you can use conditions to target specific rows.
An update statement for specific rows:
UPDATE <table> SET <column> = <value> WHERE <condition>;
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>;
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.
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 up