"Arne" <A***@discussions.microsoft.com> wrote in message
news:A9B58290-2025-4CB9-9F34-C9E4CFB736E3@microsoft.com...
>I found this statement in a program calling MS Access:
>
> UPDATE Item
>  INNER JOIN (Detail INNER JOIN Main ON Detail.DetailMainKey =
> Main.MainKey)
> ON Item.ItemSKU = Detail.DetailItemSKU
>   SET Item.ItemGroupKey = MainParentKey
>
> This syntax doesn't seem to be supported in SQL 7.0.
> Is it valid in SQL 2000 or 2005?

"Hari Prasad" wrote:

> Hi,
>
> Try this,
>
> UPDATE Item
>    SET    Item.ItemGroupKey = MainParentKey
>    FROM  (Detail INNER JOIN Main ON Detail.DetailMainKey = Main.MainKey)
>          INNER JOIN Item ON Item.ItemSKU = Detail.DetailItemSKU
>
> Thanks
> Hari
> SQL Server MVP
>
> "Arne" <A***@discussions.microsoft.com> wrote in message
> news:A9B58290-2025-4CB9-9F34-C9E4CFB736E3@microsoft.com...
> >I found this statement in a program calling MS Access:
> >
> > UPDATE Item
> >  INNER JOIN (Detail INNER JOIN Main ON Detail.DetailMainKey =
> > Main.MainKey)
> > ON Item.ItemSKU = Detail.DetailItemSKU
> >   SET Item.ItemGroupKey = MainParentKey
> >
> > This syntax doesn't seem to be supported in SQL 7.0.
> > Is it valid in SQL 2000 or 2005?
>
>
>

"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
>
> This proprietary UPDATE syntax is invalid in Standard SQL, can produce
> unpredictable results and will port to other product but produces
> different results.
>
> I also had to change the screwed data element names to something that
> looks like the programmer did a data model and not a COBOL file system
> layout.  Look at how many places data elements change names or are
> named for how they are used in a local table or flop about as
> incomplete thoughts!!
>
> UPDATE Items
>    SET item_group_id
>    = (SELECT M.item_group_id
>          FROM  Details AS D1, Mains AS M
>       WHERE D.main_id = M.main_id
>             AND Item.item_sku  = D.item_sku);
>
> You need a basic course in data modleing and to re-do all this mess at
> once.
>
>

"Thomas Coleman" wrote:

> > I am so sorry; the maniacs usually post here.  Really.
> >
> > The fifth labor of Hercules was to clean the stables of King Augeas in
> > a single day.  The Augean stables held thousands of animals and were
> > over a mile long.  This story has a happy ending for three reasons: (1)
> > Hercules solved the problem in a clever way (2) Hercules got one tenth
> > of the cattle for his work (3) At the end of the story of the Labors of
> > Hercules, he got to kill the bastard that gave him this job.
> >
> > Can you run over this guy in the parking lot?
>
> You forgot to mention that Hercules' solution was to change the course of not
> one, but two rivers in order to "solve" the problem. Of course, the myth never
> mentions that in reality this would have likely killed numerous horses and crops
> that got in the way in addition untold damage to any houses or castles that were
> in the wake of his "solution" not to mentioned what damange it would have caused
> to the people that were previously downstream of the rivers.
>
> The moral is threefold
>
> 1. Changing the course of a river is generally not possible by most people. (Not
> everyone is Hercules)
>
> 2. Even if possible flooding the kingdom, while perhaps solving one problem will
> create a host of other, probably more expensive problems. (Law of unintended
> consequences)
>
> 3. Had Hercules told the King that he might have to rebuild his kingdom after
> applying his solution to the over abundance of fertilizer, it is doubtful he
> would have gone along with the idea. (Effecting a drastic solution without
> authorization is generally not good for the health of your career).
>
>
>
> Thomas
>
>
>

"Hari Prasad" wrote:

> Hi,
>
> Try this,
>
> UPDATE Item
>    SET    Item.ItemGroupKey = MainParentKey
>    FROM  (Detail INNER JOIN Main ON Detail.DetailMainKey = Main.MainKey)
>          INNER JOIN Item ON Item.ItemSKU = Detail.DetailItemSKU
>
> Thanks
> Hari
> SQL Server MVP
>
> "Arne" <A***@discussions.microsoft.com> wrote in message
> news:A9B58290-2025-4CB9-9F34-C9E4CFB736E3@microsoft.com...
> >I found this statement in a program calling MS Access:
> >
> > UPDATE Item
> >  INNER JOIN (Detail INNER JOIN Main ON Detail.DetailMainKey =
> > Main.MainKey)
> > ON Item.ItemSKU = Detail.DetailItemSKU
> >   SET Item.ItemGroupKey = MainParentKey
> >
> > This syntax doesn't seem to be supported in SQL 7.0.
> > Is it valid in SQL 2000 or 2005?
>
>
>

"Chandra" wrote:

> Hi Arne
> you can try this way as well:
>
> UPDATE Item
> SET Item.ItemGroupKey = MainParentKey
> FROM Item
> INNER JOIN Detail ON Item.ItemSKU = Detail.DetailItemSKU
> INNER JOIN Main ON Detail.DetailMainKey = Main.MainKey
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---------------------------------------
>
>
>
> "Hari Prasad" wrote:
>
> > Hi,
> >
> > Try this,
> >
> > UPDATE Item
> >    SET    Item.ItemGroupKey = MainParentKey
> >    FROM  (Detail INNER JOIN Main ON Detail.DetailMainKey = Main.MainKey)
> >          INNER JOIN Item ON Item.ItemSKU = Detail.DetailItemSKU
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Arne" <A***@discussions.microsoft.com> wrote in message
> > news:A9B58290-2025-4CB9-9F34-C9E4CFB736E3@microsoft.com...
> > >I found this statement in a program calling MS Access:
> > >
> > > UPDATE Item
> > >  INNER JOIN (Detail INNER JOIN Main ON Detail.DetailMainKey =
> > > Main.MainKey)
> > > ON Item.ItemSKU = Detail.DetailItemSKU
> > >   SET Item.ItemGroupKey = MainParentKey
> > >
> > > This syntax doesn't seem to be supported in SQL 7.0.
> > > Is it valid in SQL 2000 or 2005?
> >
> >
> >