Warning

This book is new. If you'd like to go through it, then join the Learn Code Forum to get help while you attempt it. I'm also looking for feedback on the difficulty of the projects. If you don't like public forums, then email help@learncodethehardway.org to talk privately.

Exercise 41: Updating with SQL

You now know the CR parts of CRUD, which leaves the Update and Delete operations. As with all the other SQL commands the UPDATE command follows a format similar to DELETE, but it changes the columns in rows instead of deleting them.

1
2
3
4
5
6
7
8
UPDATE person SET first_name = "Hilarious Guy"
    WHERE first_name = "Zed";

UPDATE pet SET name = "Fancy Pants"
    WHERE id=0;

SELECT * FROM person;
SELECT * FROM pet;

In the above code I'm changing my name to "Hilarious Guy", since that's more accurate. And to demonstrate my new moniker I renamed my Unicorn to "Fancy Pants." He loves it.

This shouldn't be that hard to figure out, but just in case I'm going to break the first one down:

  1. Start with UPDATE and the table you're going to update, in this case person.
  2. Next use SET to say what columns should be set to what values. You can change as many columns as you want as long as you separate them with commas like first_name = "Zed", last_name = "Shaw".
  3. Then specify a WHERE clause that gives a SELECT style set of tests to do on each row. When the UPDATE finds a match, it does the update and SETs the columns to how you specified.

Updating Complex Data

In the last exercise I had you do a subquery in the UPDATE, and now you'll use it to change all the pets I own to be named "Zed's Pet."

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT * FROM pet;

UPDATE pet SET name = "Zed's Pet" WHERE id IN (
    SELECT pet.id
    FROM pet, person_pet, person
    WHERE
    person.id = person_pet.person_id AND
    pet.id = person_pet.pet_id AND
    person.first_name = "Zed"
);

SELECT * FROM pet;

This is how you update one table based on information from another table. There's other ways to do the same thing, but this way is the easiest to understand for you right now.

Replacing Data

I'm going to show you an alternative way to insert data that helps with atomic replacement of rows. You don't necessarily need this too often, but it does help if you're having to replace whole records and don't want to do a more complicated UPDATE without resorting to transactions.

In this situation, I want to replace my record with another guy but keep the unique id. Problem is I'd have to either do a DELETE/INSERT in a transaction to make it atomic, or I'd need to do a full UPDATE.

Another simpler way to do it is to use the REPLACE command, or add it as a modifier to INSERT. Here's some SQL where I first fail to insert the new record, then I use these two forms of REPLACE to do it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
/* This should fail because 0 is already taken. */
INSERT INTO person (id, first_name, last_name, age)
    VALUES (0, 'Frank', 'Smith', 100);

/* We can force it by doing an INSERT OR REPLACE. */
INSERT OR REPLACE INTO person (id, first_name, last_name, age)
    VALUES (0, 'Frank', 'Smith', 100);

SELECT * FROM person;

/* And shorthand for that is just REPLACE. */
REPLACE INTO person (id, first_name, last_name, age)
    VALUES (0, 'Zed', 'Shaw', 37);

/* Now you can see I'm back. */
SELECT * FROM person;

Exercise Challenge

  1. Use UPDATE to change my name back to "Zed" by my person.id.
  2. Write an UPDATE that renames any dead animals to "DECEASED". If you try to say they are "DEAD" it'll fail because SQL will think you mean 'set it to the column named "DEAD"', which isn't what you want.
  3. Try using a subquery with this just like with DELETE.
  4. Go to the SQL As Understood By SQLite page and start reading through the docs for CREATE TABLE, DROP TABLE, INSERT, DELETE, SELECT, and UPDATE.
  5. Try out some of the interesting things you find in these docs, and take notes on things you don't understand so you can research them more later.

Further Study

As usual, continue diving deep into the SQLite3 language by reading the documentation on UPDATE at https://sqlite.org/lang_update.html and related pages.

Pre-order Learn More Python The Hard Way

When you pre-order Learn More Python The Hard Way, you'll receive the Python 3 Edition as it's being created. All files are DRM free and you can download them to your computer for offline viewing. Digital Download Only! You do not get a physical book.

$29.00

Buy the Online Course

Or, you can read Learn More Python the Hard Way for free right here, video lectures not included.

Other Buying Options

Other buying options coming soon.

No content available for this exercise. You can view all available downloads at your customer account page.