H2 Database - Delete
Donald Green
The SQL DELETE query is used to remove existing records from a table. We can use WHERE clause with DELETE query to delete selected records, otherwise all records will be deleted.
Syntax
The following is the general syntax for requesting a delete command.
DELETE [ TOP term ] FROM tableName [ WHERE expression ] [ LIMIT term ]
The above syntax removes rows from a table. If TOP or LIMIT is specified, then at most the specified number of rows are deleted (no limit if zero or less than zero).
example
Consider a CUSTOMER table that has 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 data of the client whose id is 6.
DELETE FROM CUSTOMERS WHERE ID = 6;
After executing the above command, check the Customer table by running 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 records from the CUSTOMERS table, we don't use the WHERE clause. The DELETE request will look like this.
DELETE FROM CUSTOMER;
After executing the above command, no records will be available in the Customer table.
