Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Updating All Rows in a Table3:01 with Andrew Chalkley
Sometimes you need to update values in all rows. Maybe to fix an error or update the status of something. In this video we'll update data!
An update statement for all rows:
UPDATE <table> SET <column> = <value>;
= sign is different from an equality operator from a
WHERE condition. It's an assignment operator because you're assigning a new value to something.
UPDATE users SET password = "thisisabadidea"; UPDATE products SET price = 2.99;
Update multiple columns in all rows:
UDPATE <table> SET <column 1> = <value 1>, <column 2> = <value 2>;
UPDATE users SET first_name = "Anony", last_name = "Moose"; UPDATE products SET stock_count = 0, price = 0;
See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.
[MUSIC] 0:00 Let's remind ourselves of the CRUD acronym and what we know so far. 0:04 We know that to create a ruin database, we use the keyword INSERT. 0:09 For reading information, we use SELECT. 0:14 But how about UPDATE? 0:17 Unlike the other two it's not different from the word used in the acronym. 0:19 It's UPDATE. 0:23 To write an UPDATE statement, you'd write something like this. 0:27 UPDATE, the table name, a cue word we haven't used before 0:31 SET because we want to set the value of a column, next the column name, 0:35 then the equal symbol and then the value. 0:41 Now the equal sign in this case is not doing the same thing 0:45 as what it would do in a condition in a WHERE clause. 0:49 In the WHERE clause, it's the equal to or equality operator. 0:52 In the UPDATE statement, it's know as an assignment operator. 0:57 This is because we're assigning a new value 1:01 to an existing value with a given column name. 1:05 Let's see this in action. 1:08 Let's first take a look at the patrons table. 1:10 Imagine we wanted to give a copy of this database to an outsource developer and 1:12 you wanted to anonymize any sensitive data of your patrons 1:16 while giving an accurate representation of the user dataset. 1:20 Let's use the template from before and change all the last names to anonymous. 1:25 The table name is patrons. 1:34 The column name is last_name. 1:38 And then the value of the text Anonymous. 1:46 Let's execute the query. 1:56 And it updates all the last names in our table to Anonymous. 1:59 But we still have zip codes and 2:03 email addresses that could be tracked back to somebody. 2:05 Let's update both the email and the zip_code. 2:08 To do this, we can use a single statement with each assignment separated like this. 2:12 Let's write our statement now. 2:20 Let's change the table to patrons. 2:25 The first column is email. 2:28 The second column is zip_code with the relevant values. 2:32 We've got the email as email@example.com and the zip codes set to five 5s. 2:51
You need to sign up for Treehouse in order to download course files.Sign up