Bummer! You have been redirected as the page you requested could not be found.

Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Modifying Data with SQL

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Update All Rows or Columns in a Table

3: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.

SQL Used

An update statement for all rows:

UPDATE <table> SET <column> = <value>;

The = 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.

Examples:

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>;

Examples:

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.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    [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 anon@email.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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up