Update All Rows or Columns in a Table3:01 with Andrew Chalkley
Sometimes you need to use SQL to update values in all rows or columns of a table, whether to fix an error, update a status, or something else. This video will show you how to update data in SQL.
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