Guide to Permanently Deleting SQL Data (DELETE)

Meta description: Learn how to safely delete data with the SQL DELETE command, multi-table deletions, and foreign key (CASCADE / SET NULL) behavior.

🎯 What Will You Learn in This Guide?

In this guide, you will learn how to permanently delete data from the database using the DELETE command in SQL.

• Deleting from a single table or multiple tables
• Using WHERE, LIKE, JOIN and Subquery
• ON DELETE CASCADE and SET NULL behaviors
• Secure deletion methods (testing with SELECT)

---

🧠 Technical Summary

• Main topic: Data deletion in SQL
• Purpose: Permanently remove obsolete or erroneous data
• Basic steps:
  1. Creating database and tables
  2. Delete single table with DELETE syntax
  3. Bulk delete with LIKE and Subquery
  4. Deleting multiple tables with JOIN
  5. Learning the differences between ON DELETE CASCADE and SET NULL

---

⚙️ 1. Preparing the Environment

Connect to the server via SSH:

ssh admin@tr1-node01.ornek.com
mysql -u admin -p

💬 Starts the MySQL client.

CREATE DATABASE deleteDB;
USE deleteDB;

💬 Creates and activates the sample database named deleteDB.

---

🧱 2. Sample Tables

Members (members)

CREATE TABLE uyeler (
  uyeID INT PRIMARY KEY,
  ad VARCHAR(30),
  sehir VARCHAR(20)
);

Equipment (equipments)

CREATE TABLE ekipmanlar (
  ekipmanID INT PRIMARY KEY,
  ekipmanTipi VARCHAR(30),
  marka VARCHAR(20),
  sahipID INT,
  CONSTRAINT fk_sahipID FOREIGN KEY (sahipID) REFERENCES uyeler(uyeID)
);

---

💾 3. Add Sample Data

INSERT INTO uyeler VALUES
(1, 'Kerem', 'İstanbul'),
(2, 'Ayşe', 'İzmir'),
(3, 'Levent', 'Ankara');

INSERT INTO ekipmanlar VALUES
(1, 'Gitar', 'Fandar', 1),
(2, 'Mikrofon', 'Sure', 2),
(3, 'Synthesizer', 'Korgi', 3),
(4, 'Amfi', 'Peabey', 1);

---

🧹 4. Deleting Data in a Single Table

Basic Structure

DELETE FROM tablo_adi
WHERE koşullar;

⚠️ Warning: If you do not use WHERE, all data in the table will be permanently deleted.

Example:

DELETE FROM ekipmanlar
WHERE marka = 'Korgi';

💬 Deletes the registration of Korgi branded equipment.

To check before deleting:

SELECT * FROM ekipmanlar WHERE marka = 'Korgi';

---

🔁 5. Conditional Delete with LIKE and Subquery

Bulk Delete with LIKE

DELETE FROM ekipmanlar
WHERE ekipmanTipi LIKE '%Gitar%';

💬 Deletes all records containing "Guitar" in the name.

Deletion with Subquery

DELETE FROM ekipmanlar
WHERE sahipID IN (
  SELECT uyeID FROM uyeler WHERE ad LIKE 'L%'
);

💬 Deletes equipment owned by members whose names start with "L".

---

🔗 6. Deleting from Multiple Tables with JOIN

In some systems, data can be deleted from two different tables simultaneously using JOIN:

DELETE ekipmanlar, yasakliMarkalar
FROM ekipmanlar
JOIN yasakliMarkalar
ON ekipmanlar.marka = yasakliMarkalar.markaAdi
WHERE menseiUlke = 'ABD';

💬 Brands originating from the “USA” are removed from both tables.

If you just want to delete from one table:

DELETE ekipmanlar
FROM ekipmanlar
JOIN yasakliMarkalar
ON ekipmanlar.marka = yasakliMarkalar.markaAdi
WHERE menseiUlke = 'ABD';

---

🔒 7. FOREIGN KEY Behaviors (CASCADE and SET NULL)

By default, the DELETE operation returns an error if there are related records in linked tables.

Error Example

DELETE FROM uyeler WHERE uyeID = 1;

🛑 You will get the “Cannot delete or update a parent row” error.

Solution 1 – ON DELETE CASCADE

ALTER TABLE ekipmanlar
DROP FOREIGN KEY fk_sahipID;

ALTER TABLE ekipmanlar
ADD CONSTRAINT fk_sahipID
FOREIGN KEY (sahipID) REFERENCES uyeler(uyeID)
ON DELETE CASCADE;

💬 When a member is deleted, his/her equipment is also automatically deleted.

Solution 2 – ON DELETE SET NULL

ALTER TABLE ekipmanlar
ADD CONSTRAINT fk_sahipID
FOREIGN KEY (sahipID) REFERENCES uyeler(uyeID)
ON DELETE SET NULL;

💬 When the member is deleted, the equipment remains but the ownerID field becomes NULL.

---

🧨 8. DELETE vs TRUNCATE TABLE

Feature	DELETE	TRUNCATE
Delete Area	Selected rows	Full table
Undo	Possible (ROLLBACK)	Usually not possible
Logging	Each line is logged	Minimum logging
Performance	Slower	Very fast
Usage	Detailed deletion	Emptying the entire table

---

❓ Frequently Asked Questions (FAQ)

1. How can I be sure before deleting?

Run SELECT * query with the same WHERE condition and see the records to be deleted.

2. What is the difference between DELETE and TRUNCATE?

DELETE deletes rows one by one, TRUNCATE quickly empty the entire table.

3. What does ON DELETE SET NULL do?

It makes NULL the associated field of the deleted record in the child table, and the data is preserved.

4. In what cases is deletion with JOIN used?

To clear common records in two related tables.

5. Can I undo the deletion?

Only possible if you are in TRANSACTION and have not COMMITed.

---

☁️ Conclusion and Recommendation

In this guide, you learned the DELETE command, WHERE, JOIN, Subquery, CASCADE and SET NULL behaviors in SQL. You can perform secure deletions without compromising data integrity.

💡 You can try all the examples by testing them on your own MySQL server on GenixNode. GenixNode offers secure and scalable cloud infrastructure for developers. 🚀