Professional Web Applications Themes

Tricky Update - Microsoft SQL / MS SQL Server

Hi All, I have the following SQL SELECT A.Sedol,A.StartDate,A.EndDate, A.SPrice, A.SFactor, AVG(B.[SFactor]) AS PAVGFactor, SUM(B. SFactor]) AS PSUMFactor FROM tblFundFactFile_ME A LEFT OUTER JOIN tblFundFactFile_ME B ON B.[StartDate] <= A.[StartDate] AND A.Sedol = B.Sedol GROUP BY A.ID, A.Sedol,A.StartDate,A.EndDate, A.SPrice, A.SFactor ORDER BY A.ID Basically I'm using the above in a SELECT INTO statement to create a table, however, what I really want to do is add two columns to the tblFundFactFile_ME table (PAVGFactor and PSUMFactor) and use an update query to populate the values rather than create a new table. The main reason being that I have many more columns ...

  1. #1

    Default Tricky Update

    Hi All,

    I have the following SQL

    SELECT A.Sedol,A.StartDate,A.EndDate, A.SPrice, A.SFactor, AVG(B.[SFactor])
    AS PAVGFactor, SUM(B. SFactor]) AS PSUMFactor
    FROM tblFundFactFile_ME A LEFT OUTER JOIN tblFundFactFile_ME B ON
    B.[StartDate] <= A.[StartDate] AND A.Sedol = B.Sedol
    GROUP BY A.ID, A.Sedol,A.StartDate,A.EndDate, A.SPrice, A.SFactor
    ORDER BY A.ID

    Basically I'm using the above in a SELECT INTO statement to create a table,
    however, what I really want to do is add two columns to the
    tblFundFactFile_ME table (PAVGFactor and PSUMFactor) and use an update query
    to populate the values rather than create a new table. The main reason
    being that I have many more columns to add to the tblFundFactFile_ME table
    and I'm finding it hard to manage multiple SELECT INTO statements. All the
    columns will have similar queries to the above, but with differing gaps
    between the start and end dates.

    Basically I can't see how to update a table with a query similar to the
    above.

    Any help very much appreciated!

    Thanks,

    Steve


    Steve Guest

  2. #2

    Default Re: Tricky Update

    Im not sure what exactly you are looking for. if you post DDL/some sample
    records then it would be easier to give you the correct solution. What i
    assume is you are trying to update existing 2 columns of table
    tblFundFactFile_ME on the basis of the select query that you have posted,
    if this is true you can try following soution.
    Try:
    update X set PAVGFactor = Y.PAVGFactor, PSUMFactor = Y.PSUMFactor
    from tblFundFactFile_ME X join
    (SELECT A.Sedol,A.StartDate,A.EndDate, A.SPrice, A.SFactor, AVG(B.[SFactor])
    AS PAVGFactor, SUM(B. SFactor) AS PSUMFactor
    FROM tblFundFactFile_ME A LEFT OUTER JOIN tblFundFactFile_ME B ON
    B.[StartDate] <= A.[StartDate] AND A.Sedol = B.Sedol
    GROUP BY A.ID, A.Sedol,A.StartDate,A.EndDate, A.SPrice, A.SFactor) Y
    on x.sedol=y.sedol and x.startdate=y.startdate and x.enddate=y.enddate and
    x.sprice=y.sprice and x.sfactor=y.sfactor --make sure you are using right
    joins here.


    --
    -Vishal
    "Steve" <net> wrote in message
    news:phx.gbl... 
    AVG(B.[SFactor]) 
    table, 
    query 
    the 


    Vishal Guest

  3. #3

    Default Re: Tricky Update

    Hi Vishal,

    Your right I should have posted my DDL - sorry.

    However, as it happens I don't need to because that's exactly what I wanted!

    Many thanks

    Steve

    "Vishal Parkar" <com> wrote in message
    news:%phx.gbl... 
    AVG(B.[SFactor]) 
    > AVG(B.[SFactor]) 
    > table, 
    > query [/ref]
    table 
    > the 
    >
    >[/ref]


    Steve Guest

Similar Threads

  1. A Tricky One (well for me anyway)
    By DazFaz in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 2nd, 11:58 AM
  2. a tricky one...
    By Atmosfera in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: July 28th, 01:43 PM
  3. Help with Tricky UPDATE sql statements.
    By Lam in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 22nd, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139