Barbara

Delete data from database (MariaDB) with packageJS

Barbara's photo
Barbara
·

3 min read

As a packageJS developer, there would be times where you may need to delete data from a database and might need guidance hence the reason for the blog. Here's how to delete data from the database.

To clear or delete data from your MariaDB database, you can use the DELETE statement to remove specific rows or the TRUNCATE statement to remove all rows from a table. Here's how to use the DELETE statement.

Steps:

  1. Make sure you're executing commands within the mariaDB terminal or mysql terminal interface first. To do this:

    -> Run this command in the terminal mariadb -u your_username -p

    Replace your_username with your actual MariaDB username.

  2. Enter your MariaDB password when prompted.

  3. Once you are in the MariaDB command-line interface, run this command SHOW DATABASES; to see your database information.

  4. Choose the database you want to work with and use the USE statement to select it USE your_database_name; . Replace your_database_name with the actual name of the database you want to work with. The database name is mostly a 24 character with letters and numbers.

  5. Once you have selected a database, you can run queries on the tables within that database like so SHOW TABLES; . This query would show you the tables in your database.

  6. To be sure of the table you're deleting, I'd advise to see the content of the tables that way you aren't deleting the wrong table or row in the table. Run this query to do that: SELECT * FROM d0cb23f79a6b9674 .

    Replace d0cb23f79a6b9674 with the actual name of the table you're interested in. This query will retrieve all rows and columns from the specified table and you should have something like this in the terminal 👇🏾

    NOTE: In this example:

    • SELECT: Specifies the columns to be retrieved.

    • FROM: Specifies the table (your_table) from which to retrieve data.

    • WHERE: Filters the results based on specified conditions (condition1 and condition2).

Remember, the order of the clauses is important.

  1. Now that you've seen the content of the table, I'd like to delete a specific row in the table where the service_message contains "we take care of leaky air conditioning" and the service_title is "Air conditioning," you can use the DELETE statement with a WHERE clause. Here's how you can do it:

     DELETE FROM d0cb23f79a6b9674
 WHERE `service_message.=` LIKE '%we take care of leaky air conditioning%'
   AND `service_title.=` LIKE '%Air conditioning%';

    The use of % with LIKE in SQL is a wildcard character. It allows for a more flexible search and using this pattern( % ) will check for any string as "we take care of leaky air conditioning" at any position within the service_message column and do the needful.

    To be sure the data was deleted successfully, you can run the query below and you should see "empty set".

     SELECT * FROM d0cb23f79a6b9674;

    Please make sure to use backticks around column names with special characters.

    Et Voila! The query executed successfully, and no rows were affected 🎉. This is how to delete a table, rows and column in a database. This process can be replicated for any deletion process you might want to do in the future.

Until next time, ciao!!!

PackageJSMariaDB