• 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:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Update with Correlated Subquery?

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 40791
828
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
author & internet detective
Posts: 40791
828
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

you can use MERGE statement - if your DB accepts this



 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
author & internet detective
Posts: 40791
828
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Great. Thanks for sharing that it worked.
reply
    Bookmark Topic Watch Topic
  • New Topic