Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Update Table Nested Query (Using Function)

 
Sagar Dumbre
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In my query i want to update row of table "tblUser_Report". it has date field i want to update only row which has highest date value (I mean most recent date )

But i am getting error that you cant include "from" in "update"...any other way to do it ?

P.S. Database :- MySQL


 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you want a subselect

update(columns) values(...) where date = (select max(date) from table where ...)

Sory Blind this morning, didn't spot the code section.
 
Karn Kumar
Ranch Hand
Posts: 153
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The query seems to be fine .Can you try to run it in Database with params .

Also can you provide the exact JAVA code that you have written.May be problem of quotes and brackets . You can paste string query from where we can find.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
From the MySQL documentation:
Another restriction is that currently you cannot modify a table and select from the same table in a subquery.

So, unfortunately, this isn't going to work.
 
Sagar Dumbre
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wendy Gibbons wrote:you want a subselect

update(columns) values(...) where date = (select max(date) from table where ...)


Can you elaborate more on it ?
 
Sagar Dumbre
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chetan Dorle wrote:The query seems to be fine .Can you try to run it in Database with params .

Also can you provide the exact JAVA code that you have written.May be problem of quotes and brackets . You can paste string query from where we can find.


Query is Fine.
I am getting SQL Exception that "You can not include "From" in Update Query.
 
Sagar Dumbre
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:From the MySQL documentation:
Another restriction is that currently you cannot modify a table and select from the same table in a subquery.

So, unfortunately, this isn't going to work.


Any Other way to achieve the same ?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sagar Dumbre wrote:Any Other way to achieve the same ?

The easiest would probably be to split the query in two: select the maximum date for given employee_id first (into a variable in Java) and then do the update for that date and id.

On an unrelated note, you should use PreparedStatement for better performance and security, see http://www.coderanch.com/how-to/java/PreparedStatement
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sagar Dumbre wrote:
Any Other way to achieve the same ?

Just add some magic to the query to make it work:


This magic clause (select from select ) forces MySql to store a resultset from the first select (inner) in a temporary table,
and allows to bypass the restriction.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ireneusz Kordal wrote:Just add some magic to the query to make it work: ...

I'm no expert on MySQL at all, but how does this overcome the documented MySQL inability to select in a subquery from the same table which is being updated?
Edit (after your edit): oh, I see.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic