In this guide, we would look at various ways you can update and delete data in mariadb. If you are new to mariadb, you can read the following guide to follow the progression right from the start.
- Introduction To MariaDB
- [MariaDB] Guide To Creating Databases and Tables
- Altering MariaDB Tables
- Indexes In MariaDB
- Guide To Understanding INSERT Statement (MariaDB)
- Guide To Selecting Data In MariaDB
UPDATE Syntax
The UPDATE statement can be used to updates columns of existing rows in the named table with new values, it has the following syntax:
UPDATE table_name
SET column_name = value, ... ;When using the UPDATE statement, you mostly need to add a WHERE clause so as not to update all of the data in a given table.
UPDATE All Values
Suppose we have a table named cmusic and it contains the following values:
+----------+--------+-----------------+-------------------------+
| music_id | status | artiste | title |
+----------+--------+-----------------+-------------------------+
| 1 | 1 | Avicii | Silhouette |
| 2 | 1 | Beautiful Nubia | Higher Steps |
| 3 | 1 | Beautiful Nubia | Tables Turn |
| 4 | 1 | Fela | Observation Is No Crime |
| 5 | 1 | Avicii | Levels |
| 6 | 1 | Fela | Colonial Mentality |
| 7 | 1 | Asa | Jailer |
+----------+--------+-----------------+-------------------------+
To set the status value to 0, we can do:
UPDATE cmusic SET status = 0;
If we check the table, we would then have the following:
+----------+--------+-----------------+-------------------------+
| music_id | status | artiste | title |
+----------+--------+-----------------+-------------------------+
| 1 | 0 | Avicii | Silhouette |
| 2 | 0 | Beautiful Nubia | Higher Steps |
| 3 | 0 | Beautiful Nubia | Tables Turn |
| 4 | 0 | Fela | Observation Is No Crime |
| 5 | 0 | Avicii | Levels |
| 6 | 0 | Fela | Colonial Mentality |
| 7 | 0 | Asa | Jailer |
+----------+--------+-----------------+-------------------------+
The statement will set the value for the status column for all of the rows in the table.
UPDATE Specific Rows
The UPDATE statement is typically combined with a WHERE clause, this ensures you are updating the right data. The conditions of a WHERE clause in an UPDATE statement are the same as that of a SELECT statement, so, you can get an idea of what is going to be updated by using the SELECT statement before doing the actual UPDATE.
Suppose I have the following table named cmusic_producers:
+-------------+---------------+----------------------+
| producer_id | producer_name | producer_description |
+-------------+---------------+----------------------+
| 1 | Avicii | NULL |
| 2 | Cobhams | NULL |
| 3 | Eni Obanke | NULL |
| 4 | Felas' Band | NULL |
+-------------+---------------+----------------------+
Let's say I want to update the producer_description of Avicii, I can use the select statement to select it first like so:
SELECT * FROM cmusic_producers
WHERE producer_id = 1;
+-------------+---------------+----------------------+
| producer_id | producer_name | producer_description |
+-------------+---------------+----------------------+
| 1 | Avicii | NULL |
+-------------+---------------+----------------------+
To update the producer_description, I can use the producer_id to get the exact row I need, well, since an ID is unique for a person, it shouldn't be a problem:
UPDATE cmusic_producers SET producer_description = "A Swedish DJ, remixer, and Music/Recording Producer" WHERE producer_id = 1;
To check the data, we use SELECT:
SELECT * FROM cmusic_producers
WHERE producer_id = 1;
+-------------+---------------+-----------------------------------------------------+
| producer_id | producer_name | producer_description |
+-------------+---------------+-----------------------------------------------------+
| 1 | Avicii | A Swedish DJ, remixer, and Music/Recording Producer |
+-------------+---------------+-----------------------------------------------------+
It’s easy to use the UPDATE statement, especially when you know the id number of the key column for the one row you want to change.
Let's go back to the cmusic table, where we have the following:
+----------+--------+-----------------+-------------------------+
| music_id | status | artiste | title |
+----------+--------+-----------------+-------------------------+
| 1 | 0 | Avicii | Silhouette |
| 2 | 0 | Beautiful Nubia | Higher Steps |
| 3 | 0 | Beautiful Nubia | Tables Turn |
| 4 | 0 | Fela | Observation Is No Crime |
| 5 | 0 | Avicii | Levels |
| 6 | 0 | Fela | Colonial Mentality |
| 7 | 0 | Asa | Jailer |
+----------+--------+-----------------+-------------------------+To change the status of music_id 3, 4 and 5, i can do:
UPDATE cmusic SET status = 0 WHERE music_id IN(3, 4, 5);
This is the result when checked with the SELECT statement:
+----------+--------+-----------------+-------------------------+
| music_id | status | artiste | title |
+----------+--------+-----------------+-------------------------+
| 1 | 1 | Avicii | Silhouette |
| 2 | 1 | Beautiful Nubia | Higher Steps |
| 3 | 0 | Beautiful Nubia | Tables Turn |
| 4 | 0 | Fela | Observation Is No Crime |
| 5 | 0 | Avicii | Levels |
| 6 | 1 | Fela | Colonial Mentality |
| 7 | 1 | Asa | Jailer |
+----------+--------+-----------------+-------------------------+
In the above example used the IN operator to list the music_id numbers to match specific rows in the table.
UPDATE With Limits
To limit updates to a specific rows, you can use the LIMIT clause with the UPDATE statement. Here is an example:
UPDATE cmusic SET status = 1 LIMIT 4;
It will limit the update to only 4 rows, here is the result of the SELECT statement:
+----------+--------+-----------------+-------------------------+
| music_id | status | artiste | title |
+----------+--------+-----------------+-------------------------+
| 1 | 1 | Avicii | Silhouette |
| 2 | 1 | Beautiful Nubia | Higher Steps |
| 3 | 1 | Beautiful Nubia | Tables Turn |
| 4 | 1 | Fela | Observation Is No Crime |
| 5 | 0 | Avicii | Levels |
| 6 | 0 | Fela | Colonial Mentality |
| 7 | 0 | Asa | Jailer |
+----------+--------+-----------------+-------------------------+
Updating Multiple Tables
UPDATE statements can also be used to update values in one table based on values in another table, also, it is possible to update more than one table with one UPDATE statement.
Suppose we have three tables that relates with each other:
- cmusic (serves as the main table)
- cmusic_genre (Table that holds name of genre and genre description)
These are what I have in all the tables…for…
cmusic_genre
SELECT genre_id, genre_name FROM cmusic_genre;
+----------+-------------------+
| genre_id | genre_name |
+----------+-------------------+
| 1 | Indie pop |
| 2 | Progressive House |
| 3 | Folk and Root |
| 4 | Afrobeat |
+----------+-------------------+
I have more columns in the cmusic_genre, to save the display, this is all we need.
For the main table
cmusic table
SELECT music_id, artiste, title, genre_id, producer_id FROM cmusic;
+----------+-----------------+-------------------------+----------+-------------+
| music_id | artiste | title | genre_id | producer_id |
+----------+-----------------+-------------------------+----------+-------------+
| 1 | Avicii | Silhouette | 2 | 1 |
| 2 | Beautiful Nubia | Higher Steps | 3 | 3 |
| 3 | Beautiful Nubia | Tables Turn | 3 | 3 |
| 4 | Fela | Observation Is No Crime | 4 | 4 |
| 5 | Avicii | Levels | 2 | 1 |
| 6 | Fela | Colonial Mentality | 4 | 4 |
| 7 | Asa | Jailer | 1 | 2 |
+----------+-----------------+-------------------------+----------+-------------+
Looking at the table, you’ll see they have something in common, cmusic_genre has a reference id that can be linked with the cmusic table since that has its ids'.
I'll change the genre_id of Indie pop from 1 to 10 while also making sure it get updated in the main table, to do that, I'll execute:
UPDATE cmusic_genre, cmusic
SET cmusic_genre.genre_id = 10, cmusic.genre_id = 10
WHERE cmusic_genre.genre_id = 1
AND cmusic.genre_id = cmusic_genre.genre_id;
Don't get confused, this is what the SQL statement is doing:
UPDATE cmusic,genre, cmusic- This is telling UPDATE to get prepared for updates on 2 tables
SET cmusic_genre.genre_id = 10, cmusic.genre_id = 10- This set whatever we want to update, in this case, I am updating genre_id in both cmusic_genre and cmusic table to 10WHERE cmusic_genre.genre_id = 1 AND cmusic.genre_id = cmusic_genre.genre_id;In the WHERE clause, we give the condition that the genre_id from the cmusic_genre table has a value of 1 and that the genre_id in both tables equal.
In a layman, it means update cmusic_genre and cmusic table to 10 where the genre_id of the both is 1.
If we check the output of both, you'll see it has been changed...for...cmusic_genre:
SELECT * FROM cmusic_genre \G
MariaDB [marn_melodic]> SELECT * FROM cmusic_genre \G
*************************** 1. row ***************************
genre_id: 2
genre_name: Progressive House
genre_description: Progressive house is a subgenre of house music. It focuses mostly on harmonies
*************************** 2. row ***************************
genre_id: 3
genre_name: Folk and Root
genre_description: Built on rich folkloric traditions and native wisdom.
*************************** 3. row ***************************
genre_id: 4
genre_name: Afrobeat
genre_description: A music genre which involves the combination of elements of West African musical styles such as fuji music, Yoruba, and highlife.
*************************** 4. row ***************************
genre_id: 10
genre_name: Indie pop
genre_description: Indie pop or Alt-pop is a music genre and subculture that combines guitar pop with DIY ethic in opposition to the style and tone of mainstream pop music.
and for the main cmusic table:
SELECT * FROM cmusic;
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps | 3 | 3 | NULL |
| 3 | 1 | Dance | Beautiful Nubia | Tables Turn | 3 | 3 | NULL |
| 4 | 1 | Dance | Fela | Observation Is No Crime | 4 | 4 | NULL |
| 5 | 0 | Dance | Avicii | Levels | 2 | 1 | NULL |
| 6 | 0 | Dance | Fela | Colonial Mentality | 4 | 4 | NULL |
| 7 | 0 | Dance | Asa | Jailer | 10 | 2 | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
DELETE Syntax
Before you begin deleting, I want you to be aware e that there is no UNDO statement, so, whatever you are doing, make sure to have a backup in place.
The DELETE statement is similar to an UPDATE statement in that you may delete rows based on conditions in the WHERE clause.
You should always use the WHERE clause, unless you really want to leave an empty table with no rows.
If you like, you can use the LIMIT clause to limit the number of rows deleted in a table. It has the following syntax:
DELETE FROM table_name
[WHERE condition]
[ORDER BY column];The statement in the square brackets are totally optional.
DELETE Specific Row
Suppose you have the following table:
+----------+-----------------+-------------------------+
| music_id | artiste | title |
+----------+-----------------+-------------------------+
| 1 | Avicii | Silhouette |
| 2 | Beautiful Nubia | Higher Steps |
| 3 | Beautiful Nubia | Tables Turn |
| 4 | Fela | Observation Is No Crime |
| 5 | Avicii | Levels |
| 6 | Fela | Colonial Mentality |
| 7 | Asa | Jailer |
+----------+-----------------+-------------------------+
To row 7, you do:
DE
DELETE FROM cmusic
WHERE music_id = 7;This would delete row 7.
Deleting in Multiple Tables
If data in one table is dependent on another table, and you delete data from one of the tables, you'll have leftover data in the other table.
While you could execute multiple DELETE statement, it is better to delete rows in both tables in the same DELETE statement, it can make things a bit faster.
Suppose I have two tables that are related by their genre_id...the first table is:
cmusic_genre
SELECT * FROM cmusic_genre \G
*************************** 1. row ***************************
genre_id: 1
genre_name: Indie pop
genre_description: Indie pop or Alt-pop is a music genre and subculture that combines guitar pop with DIY ethic in opposition to the style and tone of mainstream pop music.
*************************** 2. row ***************************
genre_id: 2
genre_name: Progressive House
genre_description: Progressive house is a subgenre of house music. It focuses mostly on harmonies
*************************** 3. row ***************************
genre_id: 3
genre_name: Folk and Root
genre_description: Built on rich folkloric traditions and native wisdom.
*************************** 4. row ***************************
genre_id: 4
genre_name: Afrobeat
genre_description: A music genre which involves the combination of elements of West African musical styles such as fuji music, Yoruba, and highlife.
and the second table is...
cmusic
SELECT * FROM cmusic;
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps | 3 | 3 | NULL |
| 3 | 1 | Dance | Beautiful Nubia | Tables Turn | 3 | 3 | NULL |
| 4 | 1 | Dance | Fela | Observation Is No Crime | 4 | 4 | NULL |
| 5 | 0 | Dance | Avicii | Levels | 2 | 1 | NULL |
| 6 | 0 | Dance | Fela | Colonial Mentality | 4 | 4 | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
So, to delete all genre_id 3, and we would also make sure they get delete in both table, we can do it this way:
DELETE FROM cmusic_genre, cmusic
USING cmusic_genre JOIN cmusic
WHERE cmusic_genre.genre_id = 3
AND cmusic_genre.genre_id = cmusic.genre_id;Let's get down on what the SQL statement is doing:
DELETE FROM cmusic_genre, cmusic- The FROM clause lists the tables from which data is to be deleted.
USING cmusic_genre JOIN cmusic- The USING clause specifies which columns to test for equality when two tables are joined. This lists the tables and how they are joined.WHERE cmusic_genre.genre_id = 3 AND cmusic_genre.genre_id = cmusic.genre_id;In the WHERE clause, we give the condition that the genre_id from the cmusic_genre table has a value of 3 AND that the genre_id in both tables equal.
In a layman, it means delete from cmusic_genre and cmusic table where their genre_id are equivalent to 3.