BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

SQL DELETE Statement

By Chaitanya Singh | Filed Under: SQL

SQL DELETE statement is used to permanently delete existing records (rows) from the table. This statement can be used to delete one or more records from the table. We can use where clause with the DELETE statement to delete specific records from table, however in the absence of where clause, the DELETE statement permanently removes all the records from the table.

DELETE statement Syntax

To remove specific rows from the table:

DELETE 
 FROM table_name 
WHERE Condition;

To remove all the rows from the table:

DELETE 
 FROM table_name;

This statement removes all the records from table but the table itself is not deleted, thus table structure, attributes and indexes remains intact.

SQL DELETE statement Example

Table: Customers

CustomerName   Amount   PurchaseYear   OrderNo
------------   ------   ------------   -------
Paul           1500          2018      1001
Rick           1600          2019      1100
Derek          800           2017      1301
Sheru          1100          2016      1789

Lets say we want to delete the records from the table, where the PurchaseYear is prior to the year 2018.
The following SQL statement deletes the specific records from the table, where the PurchaseYear is less than 2018.

DELETE FROM Customers WHERE PurchaseYear < 2018;

Results:
The Customers table after the execution of above DELETE statement, would look like this:

CustomerName   Amount   PurchaseYear   OrderNo
------------   ------   ------------   -------
Paul           1500          2018      1001
Rick           1600          2019      1100

Lets take some more examples:

Example: Delete the record of customer where CustomerName is ‘Rick’:

DELETE FROM Customers WHERE CustomerName = 'Rick';

This statement will delete 1 record from the table.

Example: Delete the records where Amount is greater than 1000:

DELETE FROM Customers WHERE Amount > 1000;

This statement will delete 3 records from the table.

Delete all records from the table

To delete all the records from the table, we use the DELETE statement without the where clause. For example: Lets say we want to delete all the records from the table “Customers”.

DELETE FROM Customers;

This statement deletes all the rows from table but the table itself is not deleted, thus table structure, table attributes and indexes remains intact.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

SQL Tutorial

  • SQL Tutorial
  • SQL Introduction
  • SQL Syntax
  • SQL Data Types

SQL Database

  • SQL CREATE DB
  • SQL DROP DB
  • SQL Rename DB
  • SQL USE DB

SQL Queries

  • SQL Select
  • SQL Select Distinct
  • SQL Select Count
  • SQL Select Top
  • SQL Where
  • SQL AND, OR & NOT
  • SQL Order By
  • SQL Insert Into
  • SQL Insert Into SELECT
  • SQL Select Random
  • SQL Alias
  • SQL NULL Check
  • SQL Update
  • SQL Delete
  • SQL MIN, MAX
  • SQL SUM
  • SQL AVG

Copyright © 2012 – 2022 BeginnersBook . Privacy Policy . Sitemap