Oracle 10 UPDATE query 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);
The following SQL will never return any row:

select * from &lt;whatever&gt; 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 2nd 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.