Problems with UPDATE Statement

Question

I'm trying to give the most profitable employee in this lab a 9% raise.

I wrote this query to find the most profitable employee:

SELECT *
FROM (
SELECT SALESPERSONS.EmpID
  , SALESPERSONS.Ename
  , ((SUM(NVL(ORDERITEMS.Qty, 0) * NVL(INVENTORY.Price, 0)) - NVL(SALESPERSONS.Salary, 0))) AS Profit
FROM SALESPERSONS, ORDERS, ORDERITEMS, INVENTORY
WHERE SALESPERSONS.EmpID = ORDERS.EmpID (+)
AND ORDERS.OrderID = ORDERITEMS.OrderID (+)
AND ORDERITEMS.PartID = INVENTORY.PartID (+)
GROUP BY SALESPERSONS.EmpID
  , SALESPERSONS.Ename
  , SALESPERSONS.Salary
ORDER BY Profit DESC)
WHERE rownum = 1)

I've validated that that works (it's not a very big database so I was able to do this manually).

I then created an UPDATE statement to give this one employee a raise:

UPDATE SALESPERSONS
SET Salary = (
SELECT MAX(SALARY)+MAX(SALARY)*.09
FROM SALESPERSONS, (SELECT *
FROM (
SELECT SALESPERSONS.EmpID
  , SALESPERSONS.Ename
  , ((SUM(NVL(ORDERITEMS.Qty, 0) * NVL(INVENTORY.Price, 0)) - NVL(SALESPERSONS.Salary, 0))) AS Profit
FROM SALESPERSONS, ORDERS, ORDERITEMS, INVENTORY
WHERE SALESPERSONS.EmpID = ORDERS.EmpID (+)
AND ORDERS.OrderID = ORDERITEMS.OrderID (+)
AND ORDERITEMS.PartID = INVENTORY.PartID (+)
GROUP BY SALESPERSONS.EmpID
  , SALESPERSONS.Ename
  , SALESPERSONS.Salary
ORDER BY Profit DESC)
WHERE rownum = 1));

Which updates the salary for all eleven employees.

I'm trying to figure out what I'm doing wrong here. My top query only returns one result.

Your `FROM SALESPERSONS, (SELECT *...)` creates Cartesian Product. — PM 77-1, Feb 03 '15 at 03:37
I wondered about that but this:
FROM SALESPERSONS, (SELECT SALESPERSONS.Salary FROM (...returned the same results. — krebshack, Feb 03 '15 at 03:45
I know you say it's a small database but I wonder about the performance of this update query and its readability. It is interesting you reference multiple tables and use `WHERE` (implicit joins) clauses instead of `JOIN` (explicit joins) statements on IDs. See this [discussion](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause). — Parfait, Feb 03 '15 at 04:16

score 0 · Answer 1 · answered Feb 03 '15 at 03:47

The below modification should work.

UPDATE SALESPERSONS
    SET Salary = (
    SELECT MAX(SALARY)+MAX(SALARY)*.09
    FROM SALESPERSONS WHERE
    EmpID in 
     (SELECT EmpID
    FROM (
    SELECT SALESPERSONS.EmpID
      , SALESPERSONS.Ename
      , ((SUM(NVL(ORDERITEMS.Qty, 0) * NVL(INVENTORY.Price, 0)) - NVL(SALESPERSONS.Salary, 0))) AS Profit
    FROM SALESPERSONS, ORDERS, ORDERITEMS, INVENTORY
    WHERE SALESPERSONS.EmpID = ORDERS.EmpID (+)
    AND ORDERS.OrderID = ORDERITEMS.OrderID (+)
    AND ORDERITEMS.PartID = INVENTORY.PartID (+)
    GROUP BY SALESPERSONS.EmpID
      , SALESPERSONS.Ename
      , SALESPERSONS.Salary
    ORDER BY Profit DESC)
    WHERE rownum = 1));

James · Answer 2 · 2015-02-03T04:02:29.433

I'm more of a sql-server guy but I think your UPDATE doesn't have a WHERE, so it updates every row in the table getting the salary for each row using the inner query. You have to have a WHERE at the outer level that indicates which rows you want to update.

If this is a one-time query, you could just get the PK for the employee in question and use that in the WHERE.

Maybe something like this:

UPDATE SALESPERSONS
SET Salary = Salary+Salary*.09
WHERE EmpId=(
  SELECT TOP 1 SALESPERSONS.EmpID
FROM SALESPERSONS, ORDERS, ORDERITEMS, INVENTORY
WHERE SALESPERSONS.EmpID = ORDERS.EmpID
AND ORDERS.OrderID = ORDERITEMS.OrderID
AND ORDERITEMS.PartID = INVENTORY.PartID
GROUP BY SALESPERSONS.EmpID
  , SALESPERSONS.Ename
  , SALESPERSONS.Salary
ORDER BY Profit DESC);

Problems with UPDATE Statement

2 Answers2