Post reply

Update Statement

  • April 21, 2004 at 3:22 pm

    #179181

    I am trying to find a way to update multiple columns in a table using a select statement.

    I would love to be able to get this to work, but am not sure if it is possible. I've searched around the web, and BOL, but have not found an example matching what I am trying to accomplish.

    Here's what I've tried, but it's not correct:

      UPDATE @tblvarClose SET Close0PctCnt = qCnt, Close0PctGP = GP,

      Close0PctGPpct = margin

      FROM (SELECT COUNT(Quote.quote_num) AS qCnt, SUM(Quote.QuoteValue -

      VWQuoteCost.QuoteCost) AS GP, AVG(Quote.margin) AS margin

      FROM  Quote INNER JOIN

      VWQuoteCost ON Quote.quote_num = VWQuoteCost.quote_num

      WHERE (Quote.quote_date BETWEEN @sDate AND @eDate) AND

      (Quote.sales_person_num = 6) AND (Quote.close_percent = 0))

    Any help would be greatly appreciated, I'm stumped.

    Thanks,

    TomT

  • April 21, 2004 at 5:03 pm

    #503733

    1. You need to assign a "table alias" to the SELECT in the FROM clause.

    2. You need to use that table alias on all column names to the right of the "=" sign in the SET clause.

    3. You need to modify the SELECT statement so that it returns something unique that will identify the row you want to update.

    4. You need to add a WHERE clause outside the select that contains an equi-join between the table being updated and the new aliased column.

    5. Do yourself a favor... format your SQL code so that it's easier to read.  You wouldn't write VB or C in such a hap-hazard fashion.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 21, 2004 at 5:45 pm

    #503740

     >>Do yourself a favor... format your SQL code so that it's easier to read.  You wouldn't write VB or C in such a hap-hazard fashion.<<

    How would you write it?

  • April 23, 2004 at 6:16 pm

    #504099

    Could be like this:

    UPDATE

    @tblvarClose

    SET Close0PctCnt = qCnt,

    Close0PctGP = GP,

    Close0PctGPpct = margin

    FROM (SELECT COUNT(Quote.quote_num) AS qCnt,

    SUM(Quote.QuoteValue - VWQuoteCost.QuoteCost) AS GP,

    AVG(Quote.margin) AS margin

    FROM Quote INNER JOIN VWQuoteCost

    ON Quote.quote_num = VWQuoteCost.quote_num

    WHERE (Quote.quote_date BETWEEN @sDate AND @eDate)

    AND (Quote.sales_person_num = 6)

    AND (Quote.close_percent = 0))uoteCost) AS GP,

    AVG(Quote.margin) AS margin

    FROM Quote INNER JOIN VWQuoteCost

    ON Quote.quote_num = VWQuoteCost.quote_num

    WHERE (Quote.quote_date BETWEEN @sDate AND @eDate)

    AND (Quote.sales_person_num = 6)

    AND (Quote.close_percent = 0))

    Regards,

    Coach James

  • April 23, 2004 at 6:56 pm

    #504104

    Your Solution:

    UPDATE

    @tblvarClose

      SET Close0PctCnt = COUNT(Quote.quote_num),

        Close0PctGP = SUM(Quote.QuoteValue -  VWQuoteCost.QuoteCost),

      Close0PctGPpct = AVG(Quote.margin)

    FROM Quote INNER JOIN VWQuoteCost

        ON Quote.quote_num = VWQuoteCost.quote_num

    WHERE (Quote.quote_date BETWEEN @sDate AND @eDate) AND (Quote.sales_person_num = 6)

    AND (Quote.close_percent = 0)

    Hope This helps!

     

    Regards,

    Coach James

  • April 23, 2004 at 9:56 pm

    #504117

    Thanks very much for your kind assistance.

    In the interim, I wrote it like this:

    UPDATE @tblvarClose

      SET Close0PctCnt = v.qCnt,

      Close0PctGP = v.GP, Close0PctGPpct = v.margin 

    FROM (SELECT COUNT Quote.quote_num)   AS qCnt, SUM(Quote.QuoteValue -

    VWQuoteCost.QuoteCost) AS GP, AVG(Quote.margin) AS margin

    FROM  Quote INNER JOIN

       VWQuoteCost ON Quote.quote_num = VWQuoteCost.quote_num

    WHERE (Quote.quote_date BETWEEN @sDate AND @eDate)

    AND (Quote.sales_person_num = @slsID) AND (Quote.close_percent = 0)) AS v

    Would this work as well?

    Thanks again....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply