Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
share|improve this question
    
What are you trying to do with this Update statement? A description would help. –  ypercube Feb 9 '13 at 9:02
add comment

2 Answers

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 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.

share|improve this answer
    
The not null checks and NULLS LAST orderings can probably be avoided if the LEFT joins are converted into inner joins. I guess the Nulls only appear because of the SUM() over null values. –  ypercube Feb 9 '13 at 10:09
add comment

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 ;
share|improve this answer
    
I don't think your first two versions can return any rows because of the rownum = 2 thing. Am I missing something because of nesting? (Last one does look good though, better than what I hinted at.) –  Mat Feb 9 '13 at 9:19
    
@Mat: I rarely use Oracle and I avoid ROWNUM so you may be correct. That's why I added the ROW_NUMBER() way. –  ypercube Feb 9 '13 at 9:24
    
@Mat: Can you check this version of the 2nd query, would it work now? –  ypercube Feb 9 '13 at 9:46
    
I hope it does because that's essentially what I was working on :-) The not null test outside doesn't really work with the desc ordering: the nulls will be first (and ranked). Does appear to work in a test here by moving the not null check to a having clause inside the nested query. –  Mat Feb 9 '13 at 9:53
    
... but that can also be resolved with order by ... desc nulls last without a having clause. Nicer. –  Mat Feb 9 '13 at 9:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.