SRao SRao - 6 months ago 7
SQL Question

mysql- ERROR-You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

I have two tables in my database viz linkrecord(URL,NAME) and dishrate(dishname, rate,review). I want to create a 3rd table viz record which contains URL, dishname and rating from the 1st two table,in correspondance with dishname which is common to both table. I have tried the following Insert query but it shows the error: "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.NAME = dishrate.dishnameORDER BY dishrate.rate DESC' at line 1"

The query is:

String query= "INSERT INTO crawler.record (URL, Dishname, rate)"+
"SELECT linkrecord.URL, dishrate.dishname,dishrate.rate"+
"FROM linkrecord, dishrate"+
"WHERE linkrecord.NAME = dishrate.dishname"+
"ORDER BY dishrate.rate DESC";
Statement stmt=db.conn.createStatement();
stmt.executeUpdate(query);


I am unable to find the error in the above query.What should I do? Thank You

Answer

You forget the spaces and you ended up with query parts like:

INSERT INTO crawler.record (URL, Dishname,rate)<space missing here>SELECT

Correct way is:

String query= "INSERT INTO crawler.record (URL, Dishname, rate) "+
                           "SELECT linkrecord.URL, dishrate.dishname,dishrate.rate "+
                            "FROM linkrecord, dishrate "+
                            "WHERE linkrecord.NAME = dishrate.dishname "+ 
                            "ORDER BY dishrate.rate DESC";
Comments