In this post, I’ll explain the Update and Delete commands in MySQL, using the Feynman method and Marvel’s Avengers. SPOILER ALERT: If you have not seen End Game or Infinity War, there will be spoilers.
Today we will complete our “CRUD” discussion (Create, Read, Update, Delete) by explaining how to update and delete items in a MySQL table.
Before we start, below is the table we are going to work with in MySQL:
In regards to the last column “ashed”, “0” refers to the fact that these characters did not turn into ashes after Thanos’ snap. “1” means they turned into ash. For information on how to insert data into tables, check out my post on that here.
How to Update MySQL Tables
Single Changes Update
In tables, there are times we need to update information, like passwords, or subscription preferences, statuses, and addresses.
In our first example, we are going to change Thor’s location from Nidavellir to Earth. To do this, we need to enter the following code:
UPDATE avengers SET location='Earth' WHERE name='Thor';What this code is saying is:
- Update the avengers table
- Set the location to “Earth”.
- Do this in the lines where “Thor” is listed under the name column.
We get the following response:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0This means MySQL has accepted our changes. To ensure this worked, we enter the following code:
SELECT * FROM avengers WHERE name='Thor';We could have used any specific identifier to use on the “where” clause, shown above, for Thor, such as “race-type=’Asgardian'” or “ages=’999′”, since no one else on the list shares those same values. In this case, since we made changes to that one line, we need to validate the information update because it’s not readily visible, unless we select the value to read the changes.
…and we get the following result:
+-------------+------+-----------+----------+------+-------+
| avengers_id | name | race_type | location | ages | ashed |
+-------------+------+-----------+----------+------+-------+
| 7 | Thor | Asgardian | Earth | 999 | 0 |
+-------------+------+-----------+----------+------+-------+We now know the update worked.
Multiple Changes on the Same Row
We are able to make multiple changes within a row by separating the changes with commas. In this case, we need to update Ant-Man’s data, so we enter the following code:
UPDATE avengers SET ashed='0', location='Quantum Realm' WHERE avengers_id=14;In this case we’re saying the following:
- Update the following in the avengers table:
- set “ashed” to ‘0’
- set ‘location’ to ‘Quantum Realm’
- Do all of this on the row where “avengers_id” = 14.
When entered, we get the following result:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Why is Ashed=’0′ in Quotes?
If you have read my MySQL: Tables (Game of Thrones) edition post, you may be wondering why the “0” value in “ashed” was in any kind of quote. Was it a string? To give us more information we’re going to need to see what the column information is for the avengers table:
“Ashed” uses the “TinyInt” data type. Although you can assume this would be in the numerical family, you’d be correct. However, if it is a number, why is the value in quotes?
BIT and TinyInt normally hold the same amount, numerically. The only difference is that TinyInt(1) (as seen above) is in reference to a boolean value (true/false). This is a common association, not a hard rule. You can put any single numeric digit in the TinyInt(1) column and MySQL would consider it valid, however, for our purposes, would make little sense outside of 0 or 1. Since there’s no designated boolean value type in MySQL, TinyInt becomes the go-to, and we get to designate what numbers represent true or false.
But what about the quotes? For TinyInt, MySQL will accept numbers with or without quotes. The quotes are a red herring since either method is accepted as correct. Not like time going through Lang rather than Lang going through time. Now let’s get back to sample 2.
Looks like MySQL accepted the code. As usual, we need to check on our work, so we use the following select/where clause:
SELECT * FROM avengers WHERE name='Ant-Man';…and we receive the following result:
+-------------+---------+-----------+---------------+------+-------+
| avengers_id | name | race_type | location | ages | ashed |
+-------------+---------+-----------+---------------+------+-------+
| 14 | Ant-Man | Human | Quantum Realm | 49 | 0 |
+-------------+---------+-----------+---------------+------+-------+We can see that both columns “location” and “ashed” have been updated with their new values. If we chose, we could replace all the information in this column, however when we use the select/where clause, we’ll need to use the new value to ensure the changes have been made, which brings us to example 3.
Multiple Updates on Different Rows
Say we want to be politically correct and update all the lines where “Mutants” are listed and replace them with “Enhanced”, we can do that. (Yes, I realize it’s very “X-Men” to use the term “mutant”, but remember, they are all in the same universe, and despite what most folks think, “Civil War”- the comic, was about the tagging and handling of mutants, reflective of the treatment of Jews by Nazi Germany during WWII)
Below would be the following code we would use to make the update:
UPDATE avengers SET race_type='Enhanced' WHERE race_type='Mutant';…and we get the following result:
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0What this says is that 7 rows were changed. We can now use the updated ‘Enhanced” value to in our select/where clause to see which rows were changed:
SELECT * FROM avengers WHERE race_type='Enhanced';This is what we get:
The changes look good, but let’s make sure we updated the entire table:
As you can see, all the Mutants have been updated with “enhanced”, Thor has his updated location, and Ant-Man has both his location and “ashed” values updated.
Delete Command in MySQL
The coding I’m going to use for the delete command is to delete whole rows of information, not columns and not specific cells. For cell values, it’s advised to just update information if the remaining information in the cells is to still be maintained, so let’s start.
Single Row Delete
First, we want to delete those who are in New York, which in this case is Dr. Strange, from this list, since he is not an Avenger during Infinity War. This would be our code:
DELETE FROM avengers where location='New York';…and our response from MySQL is:
Query OK, 1 row affected (0.00 sec)…and when we go to check to see if it’s really gone, using our select/where clause:
SELECT * FROM avengers WHERE location='New York';…we get this:
Empty set (0.00 sec)This response tells us that there is no entry in the avengers table where “New York” is listed in the location column.
Multiple Rows
Is multiple row deletions possible in MySQL? Yes. Here is what you can and cannot do with respect to multiple row deletions.
Multi-Field Deletions
As we did with the Update command, could we use commas to add additional fields to delete multiple rows? For instance, would the code below work to delete Bucky and Black Panther from the list?
DELETE FROM avengers where location='Wakanda', name='Bucky';We know that commas work with various commands, however, this is the response we get from MySQL:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' name='Bucky'' at line 1When it comes to deletion, we are NOT able to delete different “where” fields in one line. To execute our request, we need to do the following:
DELETE FROM avengers where location='Wakanda';…and then…
DELETE FROM avengers where name='Bucky';To ensure our code worked, we can use the “Select * from avengers;” request, and we get this:
This confirms our deletion of Dr. Stranger, Black Panther, and Bucky did work.
Single Value Delete
Are we able to delete multiple rows through the use of a single value? Let’s see.
If we use the following code to delete all those that turned to ash at the end of Infinity War:
DELETE FROM avengers where ashed='1';We get the following response:
Query OK, 4 rows affected (0.04 sec)What MySQL is saying is that 4 rows have been affected by this change, so if we use the “Select * From Avengers;” code, we get this:
Notice that entries # 2, 10, 11, and 13 are no longer on this list. Also, note that the numbers in this column have not updated to compensate for the loss of these rows.
So have those lost rows been deleted? Yes. Can we recover the information in those rows? No. Unlike the Infinity Gauntlet snap of Thanos, once they are gone, that’s it. Even though MySQL doesn’t make any shifts of data, this does not mean that any of that information can be retrieved.
Primary Key Auto Increment Skips in MySQL
What if we added new entries? Will they fill in the gaps in “avengers_id”? Or would the next item replace Bucky’s old spot of #15?
If we add Maria Hill, Nick Fury’s 2nd in command into this list…
insert into avengers (name, race_type, location, ages, ashed)
values('Maria Hill', 'Human', 'Earth', 36, 0);…then request MySQL to show us where she was added, we get this:
Maria is automatically added as #18. This means her entry does not replace any of the previous people listed, but continues from the last entry made, even if it is no longer on the list.
Maria was added as #18 because when we created this table, we gave avengers_id the auto-increment attribute, which automatically numbers the entries as the columns are being filled, but can we override it and take over a now empty slot?
To test this, I’m going to add Wolverine into this table, who was part of the Infinity War comic.
insert into avengers (avengers_id, name, race_type, location, ages)
values (2, 'Wolverine', 'Enhanced', 'Earth', 999);When we pull the table, after the entry, we get this:
Yes, Wolverine is now slot #2, which previously belonged to Nick Fury. This means we are able to override the auto-increment attribute and reassign the primary key value to a new entry.
How to Delete All Entries?
If Thanos was successful at the end of End Game and was able to snap his fingers and destroy all of the Avengers, this would be the MySQL equivalent. It wouldn’t delete the framework of the table, but all those that were still in the table would be gone.
To do this, we would need to use the following code:
DELETE FROM avengers;And this is MySQL’s response to the command:
Query OK, 12 rows affected (0.03 sec)…and if we requested the entries of the avengers table (select * from avengers;), we get this:
Empty set (0.00 sec)…but as I mentioned before, the table isn’t gone, only the entries. This is proven when we use the following code:
show columns from avengers;…we get back this:
How to Delete the Entire Table and the MySQL Database?
I discussed this at the end of the “MySQL: Inserting Data into Tables (Smurfs Edition)” post, but will add it here as a quick refresher.
To delete an entire table in MySQL, you would not use the Delete command, but instead would use the “drop” command.
To delete a table, you would use the following code:
drop table avengers;You would get the following, if there was nothing left in the table:
Query OK, 0 rows affected (0.08 sec)To ensure it was deleted, we would use the command:
show tables in avengers;The database I had listed the table of avengers lived in the database of avengers. In this case, you would use whatever was the database you housed your avengers table. Since I had no other tables in this database, it’s now empty, so when I made the above request, this was MySQL’s response:
Empty set (0.00 sec)To delete an entire database in MySQL, you would use the following drop command:
drop database avengers;…and if you were successful, MySQL would come back with this:
Query OK, 0 rows affected (0.01 sec)…and you’re done! Congrats on wiping Thanos and his minions off the planet! Now go and grab some shwarma!