3 Must-know Approaches to Delete a Table

Deleting a Table in SQL can achieve in three ways. These are VIZ: DELETE, TRUNCATE, and DROP. Each has a specific reason and purpose. This post explains with sample SQL queries.

Below SQL queries perfectly work in MySQL. You can run these in other RDBMS databases with minor changes.

SQL DELETE, TRUNCATE, and DROP statements

The DELETE, TRUNCATE, and DROP what these do

• The DELETE keyword deletes the data in a table but leaves the table intact.
• The TRUNCATE keyword is a faster way to delete the data in a table. The TRUNCATE keyword also preserves the table structure.
• The DROP keyword drops the data and the table itself from a database.

SQL DELETE Table

The DELETE statement deletes all the rows from the ‘customers’ table. But it keeps the table.

DELETE from customers;

SQL TRUNCATE table

The TRUNCATE deletes all the rows and keeps the table. But it’s faster than DELETE.

TRUNCATE customers;

SQL DROP table

The DROP deletes both tables and rows.

DROP TABLE IF EXISTS customers;

DELETE a single row

SELECT *
FROM table_name
WHERE lname = 'SRINI'
LIMIT 1;
DELETE
FROM table_name
WHERE lname = 'SRINI'
LIMIT 1;

Spotlight

Delete query must run with care. Since it would delete all the rows.

SQL DELETE with CASCADE:

This SQL deletes rows from both the main table and its dependent table.

DELETE
FROM customers
Where FNAME = 'Rani'
CASCADE;

Related