Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Update Table Nested Query (Using Function)

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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


 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 153
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 https://coderanch.com/how-to/java/PreparedStatement
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic