- H2 Database Tutorial
- H2 Database - Home
- H2 Database - Introduction
- H2 Database - Installation
- H2 Database - Data Manipulation
- H2 Database - Select
- H2 Database - Insert
- H2 Database - Update
- H2 Database - Delete
- H2 Database - Backup
- H2 Database - Call
- H2 Database - Explain
- H2 Database - Merge
- H2 Database - Show
- H2 Database - Data Definition
- H2 Database - Create
- H2 Database - Alter
- H2 Database - Drop
- H2 Database - Truncate
- H2 Database - Commit
- H2 Database - Grant
- H2 Database - Savepoint
- H2 Database - Rollback
- H2 Database - JDBC Connection
- H2 Database Useful Resources
- H2 Database - Quick Guide
- H2 Database - Useful Resources
- H2 Database - Discussion
H2 Database - Delete
The SQL DELETE query is used to delete the existing records from a table. We can use WHERE clause with DELETE query to delete selected records, otherwise all the records will be deleted.
Syntax
Following is the generic query syntax of the delete command.
DELETE [ TOP term ] FROM tableName [ WHERE expression ] [ LIMIT term ]
The above syntax deletes the rows from a table. If TOP or LIMIT is specified, at most the specified number of rows are deleted (no limit if null or smaller than zero).
Example
Consider the CUSTOMER table having the following records.
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
The following command will delete the details of the customer, whose ID is 6.
DELETE FROM CUSTOMERS WHERE ID = 6;
After execution of the above command, check the Customer table by executing the following command.
SELECT * FROM CUSTOMERS;
The above command produces the following output −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
If we want to DELETE all the records from CUSTOMERS table, we do not use WHERE clause. The DELETE query would be as follows.
DELETE FROM CUSTOMER;
After executing the above command, no records will be available in the Customer table.