This week's book giveaway is in the OCAJP forum.
We're giving away four copies of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) and have Khalid A Mughal & Rolf W Rasmussen on-line!
See this thread for details.
Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can someone figure out whats wrong with this SQL

 
moiz eleven
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Bosun Bello
Ranch Hand
Posts: 1511
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Gustavo Adolpho Bonesso
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 !
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
 
moiz eleven
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Kat Lee
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
moiz eleven
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Mr. C Lamont Gilbert
Ranch Hand
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic