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 the fly likes Update with Correlated Subquery? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update with Correlated Subquery?" Watch "Update with Correlated Subquery?" New topic
Author

Update with Correlated Subquery?

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
So I have two tables that look like this:



I want to write a query that will take all records in the Indicator table that have a NULL endDate value and copy the value of endDate in the IndicatorGroup table that correspond to that indicator over, but my syntax is apparently no good. Here's what I have:



Any ideas how else I might be able to do this?

Thanks,
Corey


SCJP Tipline, etc.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29220
    
135

That look close to being right. What error are you getting?

One issue I see is that "SELECT endDate" is ambigious. I think you mean ig.enddate there.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Jeanne Boyarsky wrote:That look close to being right. What error are you getting?

One issue I see is that "SELECT endDate" is ambigious. I think you mean ig.enddate there.


Good point, although that's not the error I'm seeing. The error I get is:

Incorrect syntax near 'i'


It doesn't seem that the alias is well-liked in that position.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29220
    
135

It might be database specific. IBM says what you are doing is valid.. Whereas this one says you should use the table name rather than the alias.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Hi

you can use MERGE statement - if your DB accepts this



Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Jeanne Boyarsky wrote:Whereas this one says you should use the table name rather than the alias.


It did, after all, turn out to be just this simple. The table alias in the UPDATE statement seemed to be the problem and, once I took them out, the query ran perfectly, like this:



Thanks for the assistance.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29220
    
135

Great. Thanks for sharing that it worked.
 
 
subject: Update with Correlated Subquery?
 
Similar Threads
Hibernate Date Issue
Hibernate Select - Criteria with a Date
A little issue with <c:set ...>
How to Convert HQL to SQL Query
if condition doesn't work