SQL Server Performance

update using a nested query

Discussion in 'General Developer Questions' started by robsql, Dec 2, 2004.

  1. robsql New Member

    Hi- thanks in advance for any guidance

    I am trying to update a table with a nested query. The table and the nested query have the same number or rows with a common key. It looks something like this:

    UPdate table
    Set Revenue = (select dollars ... (select ...))
    Where office = ?????;

    The problem is the nested query works fine, but I don't seem to be able to match the rows in the table with the rows in the nested query.

    Best Regards

  2. Chappy New Member

    &gt; It looks something like this:<br /><br />Why not show us how it looks exactly ? <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  3. robsql New Member

    Here is my code.

    Update Label2
    Set Label2.Revenues =
    (Select SUM(Sdollars)
    From (Select Label2.Label_Name Label,CD_Sales.CD_ID CD_ID, Sdollars
    From Label2, CD_Sales, CD
    Where Label2.Label_Name = CD.Label_Name
    AND CD.CD_Id = CD_Sales.CD_Id)
    Group By Label)
    Where Label2.Label_Name = ???????;

    The error message generally ( i've tried this inumerable ways)is something similar to "trying to update single row from multiple rows".

    Thanks
  4. Adriaan New Member

    The multiple rows are coming from the outer derived table, which is grouping on all values for Label_Name.

    You need to move that WHERE statement into that outer derived table - in other words: put it before the GROUP BY clause.
  5. robsql New Member

    I'm not sure that would work. I should have included a description of the table and the query.

    Label2
    Label_Name
    Revenues

    Nested Query
    Label_Name
    SUM(Sdollars)

    These two have a row to row relationhip

    The Nested Query as is generates this multiple row materialized view correctly. THe problem I seem to have is how to treat this as:

    UPDATE Label2
    Set Revenues = Sdollars
    From Label2, NestedQuery
    Where Label2.Label_Name = NestedQuery.Label_Name

    Thanks again for any help
  6. Adriaan New Member

    I'm wondering if this does the trick:

    UPDATE Label2
    SET Label2.Revenues = SUM(Sdollars)
    FROM Label2 AS T1
    INNER JOIN CD ON T1.Label_Name = CD.Label_Name
    INNER JOIN CD_Sales ON CD.CD_Id = CD_Sales.CD_Id
    WHERE Label2.Label_Name = ???????
    GROUP By Label_Name
  7. robsql New Member

    I am using ORACLE and this version does not have INNER JOIN in its vocabulary. I have tried shifting the WHERE statement as you earlier suggested. I am not getting the multiple row error, but now I get an error where it does not recognize "CD.Label_Name" in line 11. I'm inexperienced - I don't imagine this is that unique a problem - there must be a solution.

    Update Label2
    Set Label2.Revenues =
    /* */
    (Select SUM(Sdollars)
    /* */
    From (Select Label2.Label_Name Label,CD_Sales.CD_ID CD_ID, Sdollars
    From Label2, CD_Sales, CD
    Where Label2.Label_Name = CD.Label_Name
    AND CD.CD_Id = CD_Sales.CD_Id)
    /* */
    Where Label2.Label_Name = CD.Label_Name
    Group By Label);
  8. mmarovic Active Member

    I guess you use Oracle 8, because Oracle 9 does have join operator.

    Try this:


    Update Label2
    Set Label2.Revenues = (Select SUM(Sdollars)
    From CD_Sales, CD
    Where Label2.Label_Name = CD.Label_Name
    AND CD.CD_Id = CD_Sales.CD_Id)
    Where Label2.Label_Name = ???????;

    Hope it works.

    Btw, this is MSSQL Server forum, not Oracle one.
  9. robsql New Member

    Correct - it is ORACLE 8. I knew this was SQL-SERVER, but you guys looked friendlier.

    This code didn't work. It needs to group to get the SUM to plug in the table.
  10. mmarovic Active Member

    No it doesn't need to group, if it didn't work that was for different reason. Can you please let me know which error is returned when you run query i suggested?<br /><br />No problem with asking for help here as far as I am concerned, but it is better to mention that you are using Oracle. This way we won't offer t-sql specific solutions and some of us know a litle bit about oracle, so we are able to work inside its restrictions. <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  11. robsql New Member

    Thanks for your gracious help.

    The error with the code as you provided was "line 6 - invalid column name

    I tried adding in a FROM statement doesn't seem to work - it just errors out stating "SQL command not properly ended

    Update Label2
    Set Label2.Revenues = (Select SUM(Sdollars)
    From CD_Sales, CD
    Where Label2.Label_Name = CD.Label_Name
    AND CD.CD_Id = CD_Sales.CD_Id)
    From Label2,CD /* error - SQL command not properly ended */
    Where Label2.Label_Name = CD.Label_Name;

  12. mmarovic Active Member

    What about adding ';' at the end of inside select statement:<br /><br /><pre>Update Label2<br />Set Label2.Revenues = (Select SUM(Sdollars)<br /> From CD_Sales, CD<br /> Where Label2.Label_Name = CD.Label_Name<br /> AND CD.CD_Id = CD_Sales.CD_Id<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />Where Label2.Label_Name = CD.Label_Name;</pre><br /><br />I haven't work with Oracle for a while. I work with Oracle 9 a year ago and Oracle 8 3 years ago. I can miss the syntax, but if I remember well correlated queries are supported with this version. So hope this syntax works, but not quite sure. <br />
  13. robsql New Member

    Doesn't like the ";" - invalid character.

    Thanks for the try!
  14. mmarovic Active Member

    Actually you can have where label2.Label_name = CD.Label_name because cd is not visible in update statetment it is visible only inside correlated query, you must put constant of variable in where part of update:


    Update Label2
    Set Label2.Revenues = (Select SUM(Sdollars)
    From CD_Sales, CD
    Where Label2.Label_Name = CD.Label_Name
    AND CD.CD_Id = CD_Sales.CD_Id)
    Where Label2.Label_Name = <Some constant or variable>;
  15. robsql New Member

    But that is the problem. I need the "variable" to be a list of names found in either the table "Label2" or in the Table "CD" or in the query that created the SUM(Sdollars). THey all have the same number of rows with the same distinct Label_Names.
  16. mmarovic Active Member

    Then just ommit where condition on update:


    Update Label2
    Set Label2.Revenues = (Select SUM(Sdollars)
    From CD_Sales, CD
    Where Label2.Label_Name = CD.Label_Name
    AND CD.CD_Id = CD_Sales.CD_Id);
  17. robsql New Member

    Isn't there a saying - Occam's Razor: the most obvious answer is generally the correct one. This is only a minor mod to that. The simplest is the the best.

    That was it. Thanks for all you help and persistence is moving me towards the light.

    Best Regards, Robert
  18. mmarovic Active Member

    You are welcome. Is it some kind of students homework? Oracle is rarely used for CD club application as far as I know.

Share This Page