SQL - Delete Command(s)

In the SQL world, databases, rows, and columns all have one thing in common: once a DELETE statement has been executed successfully against them, the data they once contained is lost forever! Be very careful with these commands and be sure to properly backup all data before proceeding with any type of DELETE command(s).


SQL offers several ways to tackle data deletion. Here are the differences.

SQL Delete Commands:

DELETE - Deletes any number of rows from a data object.
DROP - Removes table columns, tables, and all data objects SQL applications.
TRUNCATE - Empties out data without removing the object itself.

SQL - Delete

DELETE queries work much like UPDATE queries and like UPDATE, it is much advised to always use a WHERE condition when running any delete query or else you risk deleting too much data.

SQL Delete Query:

USE mydatabase;

DELETE 
FROM orders
WHERE customer = 'A+Maintenance';

SQL Results:

1 Row(s) affected

SQL - Truncate

SQL TRUNCATE is the fastest way to remove all data from a SQL table, leaving nothing but an empty shell. You might choose to use this command when all the data inside of a table needs to be removed but you'd like the table column definitions to remain intact.

SQL Truncate Table Code:

USE mydatabase;

TRUNCATE TABLE orders;

NOTE: Executing the command above will empty your table data and you will lose this data forever! If you plan on following along do not execute this query.

SQL - Drop

SQL DROP is another command that removes data from the data store. The DROP command must be performed on SQL objects including databases, tables, table columns, and SQL views. Dropping any of these objects removes them completely from your SQL application and all data contained in any of the data objects dropped are lost forever.

SQL Drop Examples:

USE mydatabase;

DROP TABLE orders;
DROP DATABASE mydatabase;
DROP VIEW viewname;
DROP INDEX orders.indexname;

-- FOR USE WITH ALTER COMMANDS
DROP COLUMN column_name
DROP FOREIGN KEY (foreign_key_name)

The above example also includes the syntax to drop table columns and foreign keys. These items are outlined in the SQL ALTER lesson.

Posted in: SQL