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 firstname.lastname@example.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
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:
- Start with UPDATE and the table you're going to update, in this case person.
- 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".
- 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
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.
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:
- Use UPDATE to change my name back to "Zed" by my person.id.
- 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.
- Try using a subquery with this just like with DELETE.
- 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.
- 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.