3 Approaches to Delete Table in SQL

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 table.

DELETE from customers;
Advertisements

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 delete both table 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 delete rows from both main table and its dependent table.

DELETE

FROM customers

Where FNAME = 'Rani'

CASCADE;

Related

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.