File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and Relational Databases and the fly likes Update Table Nested Query (Using Function) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Update Table Nested Query (Using Function)" Watch "Update Table Nested Query (Using Function)" New topic
Author

Update Table Nested Query (Using Function)

Sagar Dumbre
Greenhorn

Joined: Mar 02, 2012
Posts: 12
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

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Aug 06, 2009
Posts: 146

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

Joined: Aug 22, 2010
Posts: 3717
    
  61

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

Joined: Mar 02, 2012
Posts: 12
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

Joined: Mar 02, 2012
Posts: 12
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

Joined: Mar 02, 2012
Posts: 12
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

Joined: Aug 22, 2010
Posts: 3717
    
  61

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

Joined: Jun 21, 2008
Posts: 423
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

Joined: Aug 22, 2010
Posts: 3717
    
  61

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update Table Nested Query (Using Function)
 
It's not a secret anymore!