0

I am trying to query the database using the below query. It works fine when I run the query in mysql server. But when I run the same query in scala it throws error.

I want to get a single row in db whose cid_status is "unreserved" and cid_curprocess is 'process1'and update the same cid_status to "reserved" and get back the cid_issueid of that row

Below is the query.

val query = "SET @LastUpdateID := 0; UPDATE table_details INNER JOIN (SELECT cid_issueid FROM cen_issue_details WHERE cid_curprocess='process1' AND cid_status='unreserved' LIMIT 1) AS final ON cen_issue_details.cid_issueid=final.cid_issueid SET cen_issue_details.cid_status ='reserved',cen_issue_details.cid_issueid = (SELECT @LastUpdateID := cen_issue_details.cid_issueid);SELECT @LastUpdateID AS LastUpdateID;

val rs = statement.executeUpdate(query)

I get the below error:

Exception in thread "main" 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 'UPDATE table_details INNER JOIN (SELECT cid_issueid FROM cen_issue_details W' at line 1

Also I am not sure whether to use executeUpdate or executeQuery since the query does both SELECT and UPDATE.

Sachin Shetti
  • 110
  • 8
  • 1
    What is confusing? You are calling `executeupdate` with a string that has multiple statements. That is not the write interface function. – Gordon Linoff Feb 14 '18 at 10:56
  • Thanks for the reply, I understand that. I was confused on how to execute multiple statements. I solved it using transaction. I wanted to know if there is any better approach. – Sachin Shetti Feb 15 '18 at 06:14

1 Answers1

1

Statement.executeupdate(String) accepts only a single statement, but you have provided two.

See the docs at https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String)

This question discusses the options for doing multiple statements in a single roundtrip with JDBC: Two different prepared statements in one single batch

Rich
  • 15,048
  • 2
  • 66
  • 119