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