The DELETE articulation is utilized to erase columns of a table. Much the same as the UPDATE explanation, Apache Derby gives two kinds of Delete (grammar): looked erase and situated erase.
The looked erase articulation erases all the predetermined sections of a table.
Syntax
The linguistic structure of the DELETE explanation is as per the following −
ij> DELETE FROM table_name WHERE condition;
Example
Allow us to assume we have a table named representative with 5 records as demonstrated beneath −
ID |NAME |SALARY |LOCATION
----------------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Mumbai
5 |Trupti |45000 |Kochin
5 rows selected
The accompanying SQL DELETE articulation erases the record with name Trupti.
ij> DELETE FROM Employees WHERE Name = 'Trupti';
1 row inserted/updated/deleted
In the event that you get the substance of the Employees table, you can consider just to be records as demonstrated beneath −
ID |NAME |SALARY |LOCATION
----------------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Mumbai
4 rows selected
To erase all the records in the table, execute a similar inquiry without where statement.
ij> DELETE FROM Employees;
4 rows inserted/updated/deleted
Presently, in the event that you attempt to get the substance of the Employee table, you will get a vacant table as given beneath −
ij> select * from employees;
ID |NAME |SALARY |LOCATION
--------------------------------------------------------
0 rows selected
Delete Data using JDBC program
This segment discloses how to erase the current records of a table in Apache Derby information base utilizing JDBC application.
In the event that you need to demand the Derby network worker utilizing network customer, ensure that the worker is fully operational. The class name for the Network customer driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;passw ord=PASSWORD".
Follow the means offered underneath to erase the current records of a table in Apache Derby:/p>
Step 1: Register the driver
First and foremost, you need to enroll the driver to speak with the data set. The forName() strategy for the Class acknowledges a String esteem speaking to a class name loads it in to the memory, which consequently enlists it. Register the driver utilizing this technique.
Step 2: Get the connection
As a rule, the initial step we do to impart to the information base is to associate with it. The Connection class speaks to actual association with an information base worker. You can make an association object by conjuring the getConnection() technique for the DriverManager class. Make an association utilizing this technique.
Step 3: Create a statement object
You need to make a Statement or PreparedStatement or, CallableStatement objects to send SQL articulations to the data set. You can make these utilizing the strategies createStatement(), prepareStatement() and, prepareCall() individually. Make both of these articles utilizing the suitable strategy.
Step 4: Execute the query
In the wake of making an assertion, you need to execute it. The Statement class gives different strategies to execute an inquiry like the execute() technique to execute an explanation that profits more than one outcome set. The executeUpdate() technique executes inquiries like INSERT, UPDATE, DELETE. The executeQuery() strategy results that profits information. Use both of these techniques and execute the assertion made beforehand.
Example
Following JDBC model exhibits how to erase the current records of a table in Apache Derby utilizing JDBC program. Here, we are interfacing with a data set named sampleDB (will make on the off chance that it doesn't exist) utilizing the installed driver.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteData {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:sampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating a table and populating it
String query = "CREATE TABLE Employees("
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))";
String query = "INSERT INTO Employees("
+ "Name, Salary, Location) VALUES "
+ "('Amit', 30000, 'Hyderabad'), "
+ "('Kalyan', 40000, 'Vishakhapatnam'), "
+ "('Renuka', 50000, 'Delhi'), "
+ "('Archana', 15000, 'Mumbai'), "
+ "('Trupthi', 45000, 'Kochin'), "
+ "('Suchatra', 33000, 'Pune'), "
+ "('Rahul', 39000, 'Lucknow'), "
+ "('Trupthi', 45000, 'Kochin')";
//Executing the query
String query = "DELETE FROM Employees WHERE Name = 'Trupthi'";
int num = stmt.executeUpdate(query);
System.out.println("Number of records deleted are: "+num);
}
}
Output
On executing the above program, you will get the accompanying yield −
Number of records deleted are: 1