Mee Mee - 3 months ago 14
SQL Question

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);
}

Answer

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);
Comments