The SQL DELETE Statement: Basic Usage With Examples

vishalseafarer
Categories : Web development ,Internet
Tags : Internet web development topics php help

Page content

SQL DELETE: Syntax and Explanation

The SQL DELETE statement is used to delete rows from a table. It is used in combination with the FROM, AND and WHERE clauses to have better control over the data that is being deleted.

SQL DELETE Statement - Syntax:

The syntax for the statement is

DELETE FROM table_name WHERE some_column = some_value;

To Delete all rows, the following syntax is used.

DELETE * FROM table_name;

Breaking the Syntax in Parts:

The name of the table is entered in the table_name field. In the some_column field, enter the name of the column that you want to delete.

Use the WHERE clause to create conditions so that only deleting rows becomes easier and less complex. The statement is terminated by a semicolon(;).

Examples of the SQL DELETE Statement:

DELETE * FROM friends;

This statement will delete all the columns from the table friends.

DELETE FROM friends first_name;

This statement will delete all the data from the column first_name and age that is present in the table friends.

DELETE * FROM friends WHERE id > 2;

This statement will delete all the data from the table ‘friends’ that have their id greater than 2.

SQL DELETE Statement - Real World Examples:

To understand the DELETE statement, first let us create a database and a table in it; populate the table (fill the table with data) and then operate on it.

Create a database with any database name. In this example, the database is named hub. Write the following code in any SQL console. I am using a MySQL console.

CREATE DATABASE hub;

In order to create a table in that database, first we have to use the database. To use the database enter the following code.

USE hub;

Now create a table called ‘friends’ with four fields namely id, first_name, last_name and age. Enter the following code to create the table.

CREATE TABLE `hub`.`friends` (`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`first_name` VARCHAR( 30 ) NOT NULL ,`last_name` VARCHAR( 30 ) NOT NULL ,`age` INT( 3 ) NOT NULL) ENGINE = MYISAM ;

Populate the table using the INSERT INTO statement.

INSERT INTO `hub`.`friends` (`id`, `first_name`, `last_name`, `age`) VALUES (NULL, ‘harry’, ‘potter’, ‘20’), (NULL, ‘ron’, ‘weasley’, ‘20’), (NULL, ‘hermione’, ‘granger’, ‘20’), (NULL, ‘albus’, ‘dumbledore’, ‘70’);

Now use the DELETE statement and perform a few operations.

DELETE * FROM friends;

DELETE FROM friends WHERE last_name = weasley;

DELETE * FROM friends WHERE id > 2;

How the DELETE Statement is Different from TRUNCATE and DROP Commands:

DELETE statement deletes specific data that we want to delete.

TRUNCATE empties all the data but keeps the columns for future use.

DROP statement is used to delete the data permanently along with the column names. One should be careful while using the DROP statement.

Common Mistakes that Should be Avoided:

Don’t forget the semicolon (;) to end the statement.

Make sure that the spaces are used properly.
The DELETE statement should be used with caution as no backup is created when the data is deleted.

This post is part of the series: SQL for Beginners Series

This series of articles on SQL is aimed at those who have started programming in SQL. This series focuses on basic SQL statements like SELECT, DELETE, LIKE, REPLACE, GROUP BY and CASE.