Meaningless Drivel is fun!*
The moose likes JDBC 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 The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
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: 3610
    
  60

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: 3610
    
  60

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: 3610
    
  60

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)