1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Update with joins not working

Discussion in 'SQL PL/SQL' started by cowsquad, Oct 31, 2015.

  1. cowsquad

    cowsquad Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United States
    this is a homework question.
    I just want someone to tell me why my update clause is updating all rows in the SALESPERSON?
    I know there is a similar thread on here with the same question. But the answer given there, it does update all rows too.
    Code (SQL):
    /*
    lab04
    9. Give raise to our best salesperson(s)
    Write an update query to increase the value of the SALESPERSONS.salary column by
    15% for the most profitable salesperson(s)
    */

    UPDATE SALESPERSONS
    SET SALARY = (SELECT SALARY * 1.15


    FROM (
    SELECT  S.EMPID
            , DENSE_RANK() OVER
          (ORDER BY SUM(OI.QTY * I.PRICE) - S.SALARY DESC) DenseRank
    FROM SALESPERSONS S
    LEFT JOIN ORDERS O ON S.EMPID = O.EMPID
    LEFT JOIN ORDERITEMS OI ON O.ORDERID = OI.ORDERID
    LEFT JOIN INVENTORY I ON OI.PARTID = I.PARTID
     
    GROUP BY S.EMPID, S.SALARY
          )
    WHERE DenseRank = 1);
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Because your UPDATE contains no WHERE clause. There is only a WHERE clause in the SELECT statement that is nested inside your UPDATE statement. This becomes much more obvious if you use indentation when writing SQL. Poorly-formatted SQL is much harder to read and maintain.

    Code (Text):
    UPDATE SALESPERSONS
    SET SALARY = (SELECT SALARY * 1.15
                  FROM (SELECT  S.EMPID, DENSE_RANK() OVER
                                (ORDER BY SUM(OI.QTY * I.PRICE) - S.SALARY DESC) DenseRank
                        FROM SALESPERSONS S
                             LEFT JOIN ORDERS O ON S.EMPID = O.EMPID
                             LEFT JOIN ORDERITEMS OI ON O.ORDERID = OI.ORDERID
                             LEFT JOIN INVENTORY I ON OI.PARTID = I.PARTID
                        GROUP BY S.EMPID, S.SALARY
                       )
                  WHERE DenseRank = 1
                 );
     
  3. cowsquad

    cowsquad Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United States
    Thank you for your help. I kind of know that I missing a where clause after the last parenthesis. The thing is that I can't figure out what goes in the where clause.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    In your original post, you made the following request: "I just want someone to tell me why my update clause is updating all rows in the SALESPERSON?"

    The reason why is what I stated -- namely that there is no WHERE clause in the update statement. An update operation with no WHERE clause will always act against every row in the table. Now that I have answered the question you asked, you are indicating that you already knew that and want to know what goes in the where clause.

    What goes in the WHERE clause is a condition that evaluates to TRUE for (based on your original post) -- "...the most profitable salesperson(s)..." That's actually a fairly imprecise statement. Based on the SQL in your original post, I assume that it means the single highest performing salesperson by dollar value of items sold... or in the event of a tie in the highest dollar value -- multiple salespeople.

    At any rate -- all of the elements to generate the UPDATE you need exist in the supplied SQL. I am not going to simply provide the correct answer because you obviously do not understand the SQL required to perform this lab. If I simply provide the correct answer, then you likely still won't understand the SQL involved and my actions will have been a hindrance to your learning rather than a help. If you do not understand the lab, then you need to work with your instructor for this class so that they can assist you in learning the material.
     
  5. cowsquad

    cowsquad Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United States
    I really appreciate your help. I agree with you about me not knowing the proper SQL. One thing I know is that your words just motivated me to actually put more effort into getting this lab to work. I will soon today. Thank you for your cooperation.
     
  6. cowsquad

    cowsquad Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United States
    Thank you once again. I got this assignment to work :)