Re: Help with query

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

Date: Wed, 10 Mar 2004 17:02:35 -0000

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

Relevant Pages

  • Re: Capturing specific Header Date to GV
    ... refers to the FSO, but I cant find an example of the syntax for using it to ... import the Machine Details on line 4 and then get the other details from ... everything else by using Books on Line and postings - But I am now totally ...
    (microsoft.public.sqlserver.dts)
  • Re: Problem with form that thinks it a commandbutton (possibly?)
    ... Why does Form_View_Projects_Actionsseem to be a CommandButton? ... Are you aware that the syntax formobjectrefers to the ... To refer to an open form, ...
    (microsoft.public.access.formscoding)
  • Re: Problem with form that thinks it a commandbutton (possibly?)
    ... Why does Form_View_Projects_Actionsseem to be a CommandButton? ... Are you aware that the syntax formobjectrefers to the ... To refer to an open form, ...
    (microsoft.public.access.formscoding)
  • Re: Call function from a form
    ... If you put it a module, the code will not know what 'me' refers to, so ... Refer to the form implicitly using the syntax ... or .or invalid parenthesis. ... Private Sub CancelNewRecord_Click ...
    (microsoft.public.access.formscoding)
  • Re: Help to correct code
    ... I got stuck at the same location also, the comment refers to PrepareTest.xls ... but as the syntax is wrong please ignore where approbate. ...
    (microsoft.public.excel.programming)