Can someone figure out whats wrong with this SQL

I got this program out of a book and this code part works on Microsoft Access but on MySQL it give me an err.
First the CODE:
String updateStatement =
"UPDATE Books " +
"SET Price = Price + " + priceChange.getText() +
"WHERE Books.Publisher_Id = " +
"(SELECT Publisher_Id FROM Publishers WHERE Name = '" + publisher + "')";
The ERROR I get is:
Error java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near 'SELECT Publisher_Id FROM Publishers WHERE Name = 'Academic Press')' at line 1
Does anyone know whats wrong with the SQL statement? THanks.

I don't see anything wrong with the SQL. Since it works in MSAccess, and the error says syntax error or access violation, it may have to do with security.

Hi,
MySQL has some limitations about nested selects (sub-selects), like you are trying to do... See the link
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
explane to you these limitations...
Best Regards !

"moiz11", "gustavo_bonesso",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave

Thanks I read the info on the mySQL link, please forgive my ignorance - but how can the same statement be written in another form in SQL - without doing the subquery so that it will work? THanks again.

Hi there,
I can't think of a way of rewriting the SQL statement, but it seems that you are giving yourself the problem in the first place because you don't store the publisher_id
If you did, the performance would be alot better as well. Plus having your logic based on the publisher name means that you can update the name of the publisher while your program is live - a minor point I suppose.
Adam

This is not consistent with the error message you're getting, but it appears that there is no space between the price value priceChange.getText() and the WHERE keyword.

Originally posted by moiz11:
I got this program out of a book and this code part works on Microsoft Access but on MySQL it give me an err.
First the CODE:
String updateStatement =
"UPDATE Books " +
"SET Price = Price + " + priceChange.getText() +
"WHERE Books.Publisher_Id = " +
"(SELECT Publisher_Id FROM Publishers WHERE Name = '" + publisher + "')";
The ERROR I get is:
Error java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near 'SELECT Publisher_Id FROM Publishers WHERE Name = 'Academic Press')' at line 1
Does anyone know whats wrong with the SQL statement? THanks.

try using the keyword 'IN' instead of '=' ( as well as correct the spacing issue pointed out earlier );
String updateStatement =
" UPDATE Books " +
" SET Price = Price + " + priceChange.getText() +
" WHERE Books.Publisher_Id IN " +
" ( SELECT Publisher_Id FROM Publishers WHERE Name = '" + publisher + "')";
sometimes databases can be picky as your subquery may return more than one row, in which case you can not use =.
not sure if IN is ANSI compatible, but it works in Oracle, MSAccess, etc...
If that doesn't work, split your query into 2 SQL statements. Find the publisher ID first, then update the price using the publisher ID that you got in the first statement.
Jamie
[ May 24, 2002: Message edited by: Jamie Robertson ]

ok guys thanks for all the suggestions, but i tried the spacing idea - and that did not work, i even tried it on the command line - same result. I tried the "IN" method - again same result. this leads me to believe that it may not be an SQL syntax problem at all (since the exact same program worked perfectly fine in MSAccess). i think as the err says it may just be - access violation. now how do i give a specific USER and his PASSWORD permissions to UPDATE a database?
thanx again.

Well you can make sure pretty easily but substituting in the root user & password. If so, the MySQL docs are very good, at http://www.mysql.com/
Adam

Try being EXTRA specific, and reduce specificity as capable

The helped me with some problems in access with same names in multiple tables in a check constraint. the whole thing nullpointered me anyway, but at least the statement was right