Basic SQL Queries: Insert, Delete, and Update
To view all the SQL tutorials in chronological order, visit the SQL page
In this tutorial we’re going to cover inserting, updating, and deleting rows in our relational databases. At this point, I’m just going to gloss over creating tables and adding/moving/dropping columns because I’ve already covered that in the previous tutorial. Go back and review the previous lessons if you need to.
If you don’t already have it, create the “project1” database and select that database for use.
CREATE DATABASE project1;
Let’s create a new “pets” table in our “project1” database. We’ll use the following table structure:
Table name: pets
Columns: pet_id: Primary Key INT NOT NULL AUTO_INCREMENT,
pet_name: VARCHAR(55) NOT NULL,
We’ll add more columns later. To create this table, run the following SQL command:
CREATE TABLE pets ( pet_id INT NOT NULL AUTO_INCREMENT, pet_name VARCHAR(20) NOT NULL, PRIMARY KEY (pet_id) );
Okay, we’ve got a “pets” table with two columns. We only need to deal with “pet_name” because the auto increment takes care of pet_id for us.
Adding Entries in SQL
Time to add some entries into our database! We’ll start just by adding one row.
INSERT INTO pets (pet_name) VALUES("Snowflake");
The first set of parenthesis correspond with the parenthesis after VALUES. So INSERT INTO pets (pet_name) expects all sets of parenthesis after VALUES to have one element, which will be the pet_name. If you had INSERT_INTO pets (pet_name, family) then the VALUES would have two elements. One for pet_name, the other for family.
You can add multiple values in one query like this:
INSERT INTO pets (pet_name) VALUES("PJ"), ("Buffy"), ("Pixie"), ("Charlie");
The above command adds four rows to the pets table.
If we had three columns, we’d do something like this:
INSERT INTO table_name (col1, col2, col3) VALUES( val1, val2, val3), (val1, val2, val3);
Because each row in this case would have 3 columns, we’d need 3 values per row; Three values per set of parenthesis.
Note: You can use new lines to make things more readable.
Let’s add more columns to our pets table so we can add multiple rows with multiple columns in a single query.
Note: Be careful with NOT NULL. You might have problems if you add “NOT NULL” columns to tables with existing rows of data. NOT NULL requires something to be in the column, so if you’ve already got rows, and add a new column, the database will require you to fill all of those rows with data.
One way to fix this problem is to provide a default value, but we’ll deal with that a little later.
Okay, here’s the command to add more columns to our pets table:
ALTER TABLE pets ADD COLUMN class VARCHAR(30), ADD COLUMN age TINYINT, ADD COLUMN family VARCHAR(30);
Okay, now we have three more columns. Let’s add “Hank the 50 year old Turtle” to our table.
INSERT INTO pets (pet_name, class, age, family) VALUES ("Hank", "Reptile", 50, "Turtle");
What if someone had two Unicorns, but didn’t know what type of “Class” it was? We could just leave “Class” blank, or NULL by leaving it out!
INSERT INTO pets (pet_name, age, family) VALUES ("Susie", 12, "Unicorn"), ("Bobby", 8, "Unicorn");
Notice there’s only “pet_name”, “age”, and “family” in the first set of parenthesis because that’s the exact information we’re adding to the table. We don’t have “class” because we’re not adding info to the “class” column.
If I look at my database, I know that the pets with NULL values in the “Family” column are actually Dogs. Let’s update all the rows in the database that have NULL in the “Family” column.
UPDATE pets SET family = "Dog" WHERE family IS NULL;
In English, it says “Update the pets table. Set the family column equal to Dog in every row where the family is NULL.”
We just updated a bunch of records all at once. All rows with a “NULL” value in the Family column were changed to “Dog”.
Say the kid with Unicorns asked some friends at school, and they said “The Unicorn is a ‘Magic Mammal'” Okay. Now we can update our Unicorns!
UPDATE pets SET class = "Magic Mammal" WHERE family = "Unicorn";
again in English: “Update the pets table. Set the ‘class’ column to ‘Magic Mammal’ in every row where the ‘family’ column is equal to Unicorn.
Updating rows with NULL values is slightly different than non-null values. If the column has a value like “Unicorn” then you’ll use the = sign, but if it’s null, you use “IS NULL”. It’s just something you’ll have to either remember, or look up when you need it.
What if we wanted to update multiple pet’s ages, but each age is different? Unfortunately, that is significantly more difficult, and doesn’t belong in a beginners tutorial. If curiosity is just absolutely killing you, I’ll show one way to do this at the very end.
So how do we update multiple columns and rows at a time? We need to give all our Dogs an Age and a Class. We’ll set the class to “Mammal”, and give them an age of 3.
UPDATE pets SET class = "Mammal", age = 3 WHERE family = "Dog";
Once again in English. “Update the pets table. Set class equal to mammal, and age equal to 3 WHERE the family column is equal to Dog”.
One more way to UPDATE. Let’s be more scientific with our Class name. “Mammal” should be “Mammalia”. Instead of doing Two queries, one for the Dogs, and one for the Unicorns, we can do it in one query!
(We’ll just forget about the ‘Magic’ part of the Unicorn for now)
UPDATE pets SET class = "Mammalia" WHERE family = "Dog" OR family = "Unicorn";
There you go. We just changed the “Class” for both Unicorns and Dogs to the class of “Mammalia”. If you wanted more specific constraints, you can use “AND”. Play around with it and see for yourself.
Pixie and Charlie just had a birthday. Let’s add 1 to their age.
UPDATE pets SET age = age + 1 WHERE pet_name = "Pixie" OR pet_name = "Charlie";
In English: “Update the pets table. Set the age to itself plus one on all rows where the pet_name is equal to Pixie or the pet_name is equal to Charlie.
The “WHERE” clause evaluates to TRUE or FALSE. It will do the operation on all values that return TRUE. So every value passes through this expression, and if it comes out as TRUE, then
the “class” gets set to “Mammal” and the “age” gets set to 3. Because the WHERE clause looks for TRUE/FALSE, you can make more advanced queries and chain “AND” and “OR” statements together
in any way you need.
Dropping/Deleting Database Rows
Lets set our pets free. (Just make sure you take them to their natural habitat so you don’t make invasive species problems worse)
To DROP or delete a record, you use DELETE.
DELETE FROM pets WHERE pet_name = "Hank";
Or we can delete based on different conditions.
DELETE FROM pets WHERE pet_name = "Snowflake" OR age = 4;
Or DELETE a more specific record using AND.
DELETE FROM pets WHERE family = "Unicorn" AND age = 12;
If you wanted to delete ALL the records, you could just not specify a where clause.
DELETE FROM pets
So… We ran through a whole lot of queries, but you’re not limited to what was done here. You can create your own queries that match your specific needs. You just need to understand how all the components work, and put them together in a way that works for you.
Like everything in Programming, it’s similar to Lego. We have a bunch of small components that can be put together in billions of ways to do just about anything.
1. Have a look at the Documentation pages: (Update page, Delete page, INSERT page)
2. Play around with the queries some more on your own project and try to get a database setup with data so that it fits your own projects.
By the way, here’s an example of setting different ages to different rows in the same query. Don’t stress about not understanding this yet. This kind of query is quite a bit farther down the road.
The problem with the first query below, is that we don’t have an ELSE clause. Without an ELSE for the rows that don’t match, the query is going to set the ‘age’ columns that have no match to NULL.
UPDATE pets SET age = (CASE WHEN pet_name = "Snowflake" THEN 6 WHEN pet_name = "PJ" THEN 15 WHEN pet_name = "Buffy" THEN 14 WHEN pet_name = "Pixie" THEN 8 WHEN pet_name = "Charlie" THEN 7 END);
If you add an ELSE at the end to catch every row without a match, you can set those rows to equal themselves, that way there’s no deleted data.
UPDATE pets SET age = (CASE WHEN pet_name = "Snowflake" THEN 4 WHEN pet_name = "PJ" THEN 9 WHEN pet_name = "Buffy" THEN 15 WHEN pet_name = "Pixie" THEN 3 WHEN pet_name = "Charlie" THEN 7 ELSE age END);