Oracle 10 UPDATE query

Question

I get 0 rows updated message when I ran the query below. Why? Any ideas?

Update salespersons set salespersons.salary = salespersons.salary * 1.12 
where salespersons.empid =
(SELECT salespersons.empid 
  FROM (SELECT *
          FROM (SELECT *
                  FROM (SELECT s.empid employeeid,
                               s.ename employeename,
                               SUM(i.price * oi.qty) - s.salary Topprofit
                               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.ename, s.salary)
                ORDER BY Topprofit DESC)
         WHERE ROWNUM = 2
        ORDER BY Topprofit ASC)
 WHERE ROWNUM = 1 and Topprofit is not null);

Answer 1

The following SQL will never return any row:

select * from <whatever> where rownum = 2;

The rownum pseudo-column numbers the rows in the result set. The first candidate row before the where filter would get assigned a rownum of 1 and be discarded because of the where clause. The second candidate row would the again be assigned a rownum of 1 and be discarded, etc.

Also be careful with combining rownum and order by clauses. The default doesn't do what you want, it orders after the row numbers have been assigned. (So an order by after selecting a single row with a rownum filter doesn't make sense.) Please look at the ROWNUM documentation for examples of how to do "top N" queries.

You can select a specific row number by "materializing" it in a sub-query:

select * from (
  select foo, bar, rownum rnk
  from some_table
  order by bar, foo
) where rnk = 42;

You'd also be better off filtering out the nulls earlier if nulls are at all a possibility (filter them out in the most nested query if you can). And the above trick doesn't work that well with group by, so using the ROW_NUMBER() analytic function would be more appropriate.

Assuming you do want to give a nice raise to the employee ranked 2^nd by Topprofit, you could base your query on something like this:

SELECT
  s.empid employeeid,
  SUM(i.price * oi.qty) - s.salary Topprofit
  ROW_NUMBER() OVER (ORDER BY SUM(i.price * oi.qty) - s.salary desc) rnk
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.ename, s.salary
HAVING
  SUM(i.price * oi.qty) - s.salary is not null

Wrap that in select employeeid from (...) where rnk = 2 and you should be good to go.

Answer 2

I suppose you are trying to order the salesmen by their sales success and find the (2nd) top. Then update that guy's salary by 12%.

I think the error is in line 3:

Update salespersons set salespersons.salary = salespersons.salary * 1.12 
where salespersons.empid =
(SELECT employeeid                               --- change this
... 

But there are other issues, too, and the query can (and should) be further simplified by removing one nesting level and (as @Mat pointed out) the redundant ROWNUM=2 and adding NULLS LAST in the ordering to avoid placing nulls first. I think this (the nulls first or last) was the reason that no rows were updated in the first place:

Update salespersons set salary = salary * 1.12 
where empid =
(SELECT employeeid
 FROM (SELECT s.empid employeeid,
              s.ename employeename,
              SUM(i.price * oi.qty) - s.salary Topprofit
              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.ename, s.salary
       ORDER BY Topprofit DESC NULLS LAST)                --added NULLS LAST 
 WHERE ROWNUM = 2 and Topprofit is not null) ;

and using window functions:

UPDATE salespersons 
SET salary = salary * 1.12 
WHERE empid =
(SELECT employeeid
 FROM (SELECT s.empid employeeid,
              SUM(i.price * oi.qty) - s.salary AS Topprofit,
              ROW_NUMBER() OVER (ORDER BY SUM(i.price * oi.qty) - s.salary DESC NULLS LAST)
                  AS Rn
              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) t
 WHERE Rn = 2 AND Topprofit IS NOT NULL) ;

---

You could also write the update like this:

UPDATE 
  (SELECT s.empid,
          s.salary,
          ROW_NUMBER() OVER (ORDER BY SUM(i.price * oi.qty) - s.salary DESC) AS Rn
          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
   HAVING (SUM(i.price * oi.qty) - s.salary) IS NOT NULL) t
SET salary = salary * 1.12 
WHERE Rn = 2 ;