subquery SQL and JSP

[Chuan Ren]

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]

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]

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.

UPDATE BS_REGISTRATION SET REGISTRATION_STATUS = 'enrolled'
WHERE REGISTRATION_ID in (SELECT REGISTRATION_ID from BS_REGISTRATION where SESSION_ID= '21' and REGISTRATION_STATUS = 'waiting list' order by REGISTRATION_DATE asc limit 1 )

[Mathias Nilsson]

I don't think you should use an order by clause in an update substatement
// Javaprogrammer

[Jeanne Boyarsky]

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

[Chuan Ren]

Thank you so much. It works !