CODEWITHSUNDEEP
javasqloracle10g
Farwa Ansari
Farwa Ansari

Reputation: 75

Getting "java.sql.SQLSyntaxErrorException: ORA-00928: missing SELECT keyword" on deleting Record from multiple tables

I am trying to delete rows against attendnt_N using java but getting this exception java.sql.SQLSyntaxErrorException: ORA-00928: missing SELECT keyword. If anyone knows about its removal/solution please let me know. Here's my code:

ResultSet rss = stmt.executeQuery("select * from ATTENDANCE, ATTENDANTS");
    System.out.println(attendnt_N);   //working fine
    int countd = 0;
        while(rss.next()){
            countd++;
            if(attendnt_N.equalsIgnoreCase(rss.getString(1))){
                String dell = ("(DELETE FROM ATTENDANCE, ATTENDANTS WHERE Attendant_Name ='" + attendnt_N + "')");
                //String del = ("(DELETE FROM ATTENDANCE WHERE Attendant_Name ='" + attendnt_N + "')");   
                //String dell = ("(DELETE FROM ATTENDANTS WHERE Attendant_Name ='" + attendnt_N + "')");
                //stmt.executeQuery(del);
                stmt.executeQuery(dell);
                JOptionPane.showMessageDialog(null, "Record against "+ attendnt_N +" is Deleted");
                System.out.println("Deleted");
            }           
        }
    if(countd == 0){
        JOptionPane.showMessageDialog(null, "Record against "+ attendnt_N +" does not exist");
        System.out.println("Not Deleted");
        faceFrame.repaint();
        delBut.setEnabled(true);
    }

Upvotes: 0

Views: 271

Answers (3)

davidxxx
davidxxx

Reputation: 131346

First, you should suppress your commented sql queries when your post, it makes the code harder to read.

Tim Biegeleisen is right. You should do only one delete by table but you also need to change the method used with your statement. stmt.executeQuery(dell) is for selection query.
For updating, use rather stmt.executeUpdate(dell) Look at the documentation :

ResultSet executeQuery(String sql)

Executes the given SQL statement, which returns a single ResultSet object.

int executeUpdate(String sql)

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Oracle only supports deleting from one table at a time, and I believe HQL also has this limitation. So you should use two separate DELETE statements:

String del1 = ("DELETE FROM ATTENDANCE WHERE Attendant_Name ='" + attendnt_N + "'";
String del2 = ("DELETE FROM ATTENDANTS WHERE Attendant_Name ='" + attendnt_N + "'";
stmt.executeUpdate(dell);
stmt.executeUpdate(del2);

Upvotes: 2

Prahalad Deshpande
Prahalad Deshpande

Reputation: 4767

The correct way to perform the delete of records is already provided in the answer by user Tim Biegeleisen. While, the question does not shed much light about what your table structure is there is one potential performance glitch that you would run into: m The SELECT query that you are using will result in a CROSS JOIN. Hence the SELECT statement below should be changed to use a more restrictive join such as an INNER JOIN.

stmt.executeQuery("select * from ATTENDANCE, ATTENDANTS");

should be replaced with something like

stmt.executeQuery("select * from ATTENDANCE, ATTENDANTS WHERE ATTENDENCE.ATTENDANT_NAME = ATTENDANTS.ATTENDANT_NAME AND ATTENDANTS.ATTENDANT_NAME = 'attendant1'").

But this I think is unnecessary. Using Tim's technique you can efficiently delete the required records without much complications and performance implications.

Upvotes: 0

Related Questions