Are you trying to Delete Data in SQL?

This guide will help you.

The SQL DELETE Query is used to delete the existing records from a table.

In SQL, the DELETE statement is one of the most powerful operations available to users.

The DELETE operations irreversibly delete one or more rows of data from a database table.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our  Customers to manage SQL database servers.

In this context, we shall look into how the DELETE statement works.

How to Delete Data in SQL using the DELETE statement ?

Now we will see how the DELETE statements can be used to delete data in the SQL server.

We will be doing this on a Ubuntu 20.04 server with a non-root user with administrative privileges and a firewall configured with UFW.

Also, we need to ensure that server has some type of relational database management system (RDBMS) that uses SQL.

The steps which our Support Experts follow for deleting data in SQL server is given below.

1. Connecting To MySQL and Setting Up a Sample Database

If the SQL database system runs on a remote server we need to SSH into the server from the local machine:

$ ssh ibmimedia@your_server_ip
$ mysql -u ibmimedia -p

Then we will create a database named deleteDB using the following command:

mysql> CREATE DATABASE deleteDB;

We will receive the following output on the successful creation of the database:

Output

Query OK, 1 row affected (0.01 sec)

For selecting the deleteDB database, we can run the following USE statement:

mysql> USE deleteDB;

Output

Database changed

Here we will create a table named clubMembers that has these four columns:

mysql> CREATE TABLE clubMembers (
mysql> memberID int AUTO_INCREMENT PRIMARY KEY,
mysql> name varchar(30),
mysql> homeBorough varchar(15),
mysql> email varchar(30)
mysql> );

Next, we will create a table with the following columns as clubEquipment:

mysql> CREATE TABLE clubEquipment (
mysql> equipmentID int AUTO_INCREMENT PRIMARY KEY,
mysql> equipmentType varchar(30),
mysql> brand varchar(15),
mysql> ownerID int,
mysql> CONSTRAINT fk_ownerID
mysql> FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
mysql> );

After that we will run the following INSERT INTO statement to load the clubMembers table with six rows of sample data:

mysql> INSERT INTO clubMembers (name, homeBorough, email)
mysql> VALUES
mysql> ('Rosetta', 'Manhattan', 'hightower@example.com'),
mysql> ('Linda', 'Staten Island', 'lyndell@example.com'),
mysql> ('Labi', 'Brooklyn', 'siffre@example.com'),
mysql> ('Bettye', 'Queens', 'lavette@example.com'),
mysql> ('Phoebe', 'Bronx', 'snow@example.com'),
mysql> ('Mariya', 'Brooklyn', 'takeuchi@example.com');

Likewise, we will run another INSERT INTO statement to load the clubEquipment table with twenty rows of sample data:

mysql> INSERT INTO clubEquipment (equipmentType, brand, ownerID)
mysql> VALUES
mysql> ('electric guitar', 'Gilled', 6),
mysql> ('trumpet', 'Yemehe', 5),
mysql> ('drum kit', 'Purl', 3),
mysql> ('mixer', 'Bearinger', 3),
mysql> ('microphone', 'Sure', 1),
mysql> ('bass guitar', 'Fandar', 4),
mysql> ('acoustic guitar', 'Marten', 6),
mysql> ('synthesizer', 'Korgi', 4),
mysql> ('guitar amplifier', 'Vax', 4),
mysql> ('keytar', 'Poland', 3),
mysql> ('acoustic/electric bass', 'Pepiphone', 2),
mysql> ('trombone', 'Cann', 2),
mysql> ('mandolin', 'Rouge', 1),
mysql> ('electric guitar', 'Vax', 6),
mysql> ('accordion', 'Nonher', 5),
mysql> ('electric organ', 'Spammond', 1),
mysql> ('bass guitar', 'Peabey', 1),
mysql> ('guitar amplifier', 'Fandar', 3),
mysql> ('cello', 'Yemehe', 2),
mysql> ('PA system', 'Mockville', 5);

2. Deleting Data in SQL from a Single Table

Generally, the syntax for deleting data in SQL looks like the following:

mysql> DELETE FROM table_name
mysql> WHERE conditions_apply;

However, the WHERE clause is optional. If we omit the WHERE clause, the DELETE statement will delete all rows in the table.

To delete all rows in a table without the need of knowing how many rows deleted, we can use the TRUNCATE TABLE statement.

To find what instruments in  the table are made by Korgi, we will run the following query.

mysql> SELECT * FROM clubEquipment
mysql> WHERE brand = 'Korgi';

This query returns every column from the clubEquipment table, but only returns rows whose brand column contains the value Korgi:

Output

+-------------+---------------+-------+---------+
| equipmentID | equipmentType | brand | ownerID |
+-------------+---------------+-------+---------+
| 8 | synthesizer | Korgi | 4 |
+-------------+---------------+-------+---------+
1 row in set (0.00 sec)

To delete this row we can run a DELETE operation that has FROM and WHERE clauses identical to the previous SELECT statement:

mysql> DELETE FROM clubEquipment
mysql> WHERE brand = 'Korgi';

Output

Query OK, 1 row affected (0.01 sec)

This output indicates that the DELETE operation only affected a single row. 

However, we can delete multiple rows of data with any WHERE clause that returns more than one row.

The following SELECT query returns every record in the clubEquipment table whose equipmentType column contains the word electric:

mysql> SELECT * FROM clubEquipment
mysql> WHERE equipmentType LIKE '%electric%';

Output

+-------------+------------------------+-----------+---------+
| equipmentID | equipmentType | brand | ownerID |
+-------------+------------------------+-----------+---------+
| 1 | electric guitar | Gilled | 6 |
| 11 | acoustic/electric bass | Pepiphone | 2 |
| 14 | electric guitar | Vax | 6 |
| 16 | electric organ | Spammond | 1 |
+-------------+------------------------+-----------+---------+
4 rows in set (0.00 sec)

Again, to delete these four records, we can rewrite this query operation and replace SELECT * with DELETE:

mysql> DELETE FROM clubEquipment
mysql> WHERE equipmentType LIKE '%electric%';

Output

Query OK, 4 rows affected (0.00 sec)

3. Deleting Data from Multiple Tables

We can delete data from more than one table in a single operation by including a JOIN clause.

JOIN clauses will combine rows from two or more tables into a single query result.

They do this by finding a related column between the tables and sorting the results appropriately in the output.

The syntax for a DELETE operation that includes a JOIN clause as given below:

mysql> DELETE table_1, table_2
mysql> FROM table_1 JOIN table_2
mysql> ON table_2.related_column = table_1.related_column
mysql> WHERE conditions_apply;

We will run the following statement to create a table named prohibitedBrands in which we will list the brands that are no longer acceptable for the club.

mysql> CREATE TABLE prohibitedBrands (
mysql> brandName varchar(30),
mysql> homeCountry varchar(30)
mysql> );

Then load this new table with some sample data as given below:

mysql> INSERT INTO prohibitedBrands
mysql> VALUES
mysql> ('Fandar', 'USA'),
mysql> ('Givson', 'USA'),
mysql> ('Muug', 'USA'),
mysql> ('Peabey', 'USA'),
mysql> ('Yemehe', 'Japan');

Following that, the club decides to delete any records of equipment from the clubEquipment table whose brands appear in the prohibitedBrands table and are based in the United States.

We can use the following statements to implement this.

<mysql> SELECT *
mysql> FROM clubEquipment JOIN prohibitedBrands
mysql> ON clubEquipment.brand = prohibitedBrands.brandName
mysql> WHERE homeCountry = 'USA';
Output
+-------------+---------------+--------+---------+-----------+-------------+
| equipmentID | equipmentType | brand | ownerID | brandName | homeCountry |
+-------------+---------------+--------+---------+-----------+-------------+
| 6 | bass guitar | Fandar | 4 | Fandar | USA |
| 17 | bass guitar | Peabey | 1 | Peabey | USA |
+-------------+---------------+--------+---------+-----------+-------------+
2 rows in set (0.00 sec)

To delete these brands from the prohbitedBrands table and the associated equipment from clubEquipment, we canrewrite the previous SELECT statement but replace SELECT * with DELETE followed by the names of both tables:

mysql> DELETE clubEquipment, prohibitedBrands
mysql> FROM clubEquipment JOIN prohibitedBrands
mysql> ON clubEquipment.brand = prohibitedBrands.brandName
mysql> WHERE homeCountry = 'USA';

Output

Query OK, 4 rows affected (0.01 sec)

[Need urgent assistance with your SQL server? – We can help you. ]