"DELETE FROM" Statements

This section describes how to delete existing data rows with DELETE statements.

DELETE statements are used less frequently in database applications. They are used only when you want to remove data rows from a table permanently. DELETE statements should include a WHERE clause to identify the data rows to be deleted.

DELETE statements should be executed with the executeUpdate() method. The Java tutorial program below shows you how to delete rows.

/* DerbyDeleteRows.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class DerbyDeleteRows {
  public static void main(String [] args) {
    Connection con = null;
    try {
      con = DriverManager.getConnection("jdbc:derby://localhost/TestDB");
      Statement sta = con.createStatement();

// deleting multiple rows
      int count = sta.executeUpdate(
        "DELETE FROM Profile WHERE ID in (1, 3, 5, 7)");
      System.out.println("Number of rows deleted: "+count);

// getting the data back
      ResultSet res = sta.executeQuery("SELECT * FROM Profile");
      System.out.println("List of Profiles: ");
      while (res.next()) {
         System.out.println(
           "  "+res.getInt("ID")
           + ", "+res.getString("FirstName")
           + ", "+res.getString("LastName")
           + ", "+res.getFloat("Point")
           + ", "+res.getDate("BirthDate")
           + ", "+res.getTimestamp("ModTime"));
      }
      res.close();

      sta.close();
      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

The output confirms that 4 rows were deleted:

Number of rows deleted: 4
List of Profiles:
2, Janet, Gates, 999.99, 2004-10-13, 2016-12-31 23:59:59.999
4, 256d, aa3f5, 203.51392, 1999-12-31, 2016-12-31 23:59:59.999
6, 8ef, 466ca, 31.71128, 1999-12-31, 2016-12-31 23:59:59.999
8, 10cc, ab361, 325.30505, 1999-12-31, 2017-04-01 23:59:59.999
9, 11bd, 99552, 315.1111, 1999-12-31, 2017-04-01 23:59:59.999
10, 8c4, 68909, 23.095905, 1999-12-31, 2017-04-01 23:59:59.999
11, 17e1, 99a5d, 186.09494, 1999-12-31, 2017-04-01 23:59:59.999
12, 1ea2, 480c7, 131.6408, 1999-12-31, 2017-04-01 23:59:59.999
13, Keith, Harris, 0.0, 2010-03-01, 2017-04-01 23:59:59.999

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Installing and Running Java DB - Derby

 Derby (Java DB) JDBC Driver

 Derby (Java DB) JDBC DataSource Objects

Java DB (Derby) - DML Statements

 Tables with Primary Key Column "GENERATED ... AS IDENTITY"

 "INSERT INTO" Statements

 "INSERT INTO" Statements with INDENTITY Columns

 Handling Date and Timestamp Values

 "UPDATE" Statements

"DELETE FROM" Statements

 Java DB (Derby) - ResultSet Objects of Queries

 Java DB (Derby) - PreparedStatement

 Summary of JDBC Drivers and Database Servers

 Using Connection Pool with JDBC

 Archived Tutorials

 References

 Full Version in PDF/EPUB