Re: Help with query

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

Date: Wed, 10 Mar 2004 09:12:28 -0800

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