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;
SQL TRUNCATE table
The TRUNCATE deletes all the rows, and keeps the table. But it’s faster than DELETE.
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;
SQL DELETE with CASCADE:
This SQL delete rows from both main table and its dependent table.
DELETE FROM customers Where FNAME = 'Rani' CASCADE;