File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes subquery SQL and JSP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "subquery SQL and JSP" Watch "subquery SQL and JSP" New topic
Author

subquery SQL and JSP

Chuan Ren
Ranch Hand

Joined: Aug 04, 2003
Posts: 42
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

Joined: Jul 31, 2003
Posts: 263
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.


Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29245
    
139

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Mathias Nilsson
Ranch Hand

Joined: Oct 13, 2003
Posts: 107
I don't think you should use an order by clause in an update substatement
// Javaprogrammer


SCJP2 , MCP( 70-229 ) , Preparing For SCWDC
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29245
    
139

Good point. I didn't see the "order by" when I cut & pasted the code.
Chuan Ren
Ranch Hand

Joined: Aug 04, 2003
Posts: 42
Thank you so much. It works !
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: subquery SQL and JSP
 
Similar Threads
How do I format this query using JSP?
Help with JPA query please...
Question on locks and AutoCommit
please help How to Write Sub Query in Hibernate
Update with subquery