• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

subquery SQL and JSP

 
Chuan Ren
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to get a subquery working, but somehow I am stuck. Can you please let me know what I have done wrong?
This is what I want to achive but doesn't work. Can you please assist me. Thanks.
UPDATE BS_REGISTRATION SET REGISTRATION_STATUS = 'enrolled'
WHERE REGISTRATION_ID = (SELECT REGISTRATION_ID from BS_REGISTRATION where SESSION_ID= '21' and REGISTRATION_STATUS = 'waiting list' order by REGISTRATION_DATE asc limit 1 )
If I seperate them, it works properly. ie.
UPDATE BS_REGISTRATION SET REGISTRATION_STATUS = 'enrolled'
WHERE REGISTRATION_ID = 11
SELECT REGISTRATION_ID from BS_REGISTRATION where SESSION_ID= '21' and REGISTRATION_STATUS = 'waiting list' order by REGISTRATION_DATE asc limit 1
---- The following works but not too good idea---
<%
String select1 = "SELECT REGISTRATION_ID from BS_REGISTRATION where REGISTRATION_STATUS = 'waiting list' AND SESSION_ID = '"+ sid +"' order by REGISTRATION_DATE asc limit 1 ";

ResultSet rrset = CM.executeQuery(select1);
rrset.first();

String nextID = rrset.getString("REGISTRATION_ID");

String updateReg ="UPDATE BS_REGISTRATION SET REGISTRATION_STATUS = 'enrolled' "+
"WHERE REGISTRATION_ID =" + nextID + "";

CM.executeUpdate(updateReg);
-------------------------------------------
 
Tom Blough
Ranch Hand
Posts: 263
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you sure your underlying database supports subqueries? Java passes the SQL to the underlying databases ODBC driver, so chances are if it is not working, then the database does not support subqueries. IIRC, MySQL and a few others do not currently support them.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some databases don't let you use an = for the subquery (because the subquery is not guaranteed to return a unique value. Instead use an "in" clause.
 
Mathias Nilsson
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think you should use an order by clause in an update substatement
// Javaprogrammer
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good point. I didn't see the "order by" when I cut & pasted the code.
 
Chuan Ren
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you so much. It works !
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic