Re: Help with query

From: Newbie (noidea_at_nospam.com)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 07:59:45 -0000

Thanks for your help
"Rohtash Kapoor" <rohtash_nospam@sqlmantra.com> wrote in message
news:eP1SeLsBEHA.744@TK2MSFTNGP10.phx.gbl...
> UPDATE SO
> SET TotalQty =
> (SELECT SUM(OrderQty) FROM SODet
> WHERE LineType = '1' AND
> SalesOrder = SO.SalesOrder)
> ---
> Rohtash Kapoor
> http://www.sqlmantra.com
>
>
> "Newbie" <noidea@nospam.com> wrote in message
> news:%23s$2%23FsBEHA.3184@TK2MSFTNGP09.phx.gbl...
> > Thanks for your help so far . .
> >
> > The '1' refers to a LineType as opposed to a line number and therefore
> needs
> > to be included in the syntax
> >
> > Thanks again
> >
> > "Rohtash Kapoor" <rohtash_nospam@sqlmantra.com> wrote in message
> > news:emtDAAsBEHA.3360@TK2MSFTNGP11.phx.gbl...
> > > First of all I need to know why you are adding the OrderQty for only
one
> > > line item (Line = '1')?
> > >
> > > To update SO table for all the records try the following query:
> > >
> > > UPDATE SO
> > > SET TotalQty =
> > > (SELECT SUM(OrderQty) FROM SODet
> > > WHERE SalesOrder = SO.SalesOrder)
> > >
> > > (Note: The conditon Line = '1' is not included)
> > >
> > > ---
> > > Rohtash Kapoor
> > > http://www.sqlmantra.com
> > >
> > >
> > > "Newbie" <noidea@nospam.com> wrote in message
> > > news:%23hDpg1rBEHA.2380@TK2MSFTNGP10.phx.gbl...
> > > > Thanks I will give it a go . . . .
> > > >
> > > > How would the syntax change if I wanted to update all records where
> the
> > > > SO.SalesOrder = SODet.SalesOrder? where would the join expression
be?
> > > >
> > > > Thanks again
> > > > "Rohtash Kapoor" <rohtash_nospam@sqlmantra.com> wrote in message
> > > > news:%23qELEgrBEHA.744@TK2MSFTNGP10.phx.gbl...
> > > > > UPDATE SO
> > > > > SET TotalQty =
> > > > > (SELECT SUM(OrderQty) FROM SODet
> > > > > WHERE (SalesOrder = '12345') AND (Line = '1')
> > > > > GROUP BY SalesOrder)
> > > > > WHERE SalesOrder = '12345'
> > > > >
> > > > > ---
> > > > > Rohtash Kapoor
> > > > > http://www.sqlmantra.com
> > > > >
> > > > >
> > > > > "Newbie" <noidea@nospam.com> wrote in message
> > > > > news:ulmBmOrBEHA.1964@TK2MSFTNGP11.phx.gbl...
> > > > > > Hi,
> > > > > >
> > > > > > I want to be able to update a field in one table with the sum of
a
> > > field
> > > > > > from another table but i don't know how to link the two
> > > > > >
> > > > > > Here is what I have so far to get the TotalQty
> > > > > > *****TOTAL QRY*****
> > > > > > SELECT SalesOrder,SUM(OrderQty) AS TotalQty
> > > > > > FROM SODet
> > > > > > WHERE (SalesOrder = '12345') AND (Line = '1')
> > > > > > GROUP BY SalesOrder
> > > > > >
> > > > > > I then want to use the result of the above to update the SO
table
> > > where
> > > > > the
> > > > > > SO.SalesOrder = SODet.SalesOrder
> > > > > >
> > > > > > i.e Result of TOTAL QRY = 30000
> > > > > > therefore SO.Total = 30000
> > > > > >
> > > > > > How can I do this?
> > > > > > Thanks
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>