Re: Help with query

From: Rohtash Kapoor (rohtash_nospam_at_sqlmantra.com)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 08:56:43 -0800

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
> > >
> > >
> >
> >
>
>