• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Update with Correlated Subquery?

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • 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: 40198
816
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • 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
  • 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: 40198
816
Eclipse IDE VI Editor Java
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • 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
  • 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: 40198
816
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great. Thanks for sharing that it worked.
 
Seriously? That's what you're going with? I prefer 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
    Bookmark Topic Watch Topic
  • New Topic