Update with row_number() in Oracle with multiple rows

Question

Here is the deal. I have a table with data. I need to populate a field with the row occurence based on certain fields. I may have one row, I may have 10. I need to populate with that number. I have been using rownumber() to determine in a select what the number is. However updating the table has been problematic. I have tried joins, that with tablename as, then a select, then the update. Nothing has worked.

This code:

update Rptdata
SET c98 = (SELECT R from 
                (select rs1.c1, rs1.c5,rs1.c3, 
                row_number() over(partition by rs1.c1,0, rs1.c5,rs1.c3 order by rs1.c1,rs1.c5,rs1.c3 desc) as R
                from Rptdata rs1
                where rs1.c1 = c1
                and   rs1.c5 = c5
                and   rs1.c3 = c3
         --       and   rs1.c6 = c6
         --       and   rs1.c7 = c7
         --       and   rs1.c8 = c8
         --       and   rs1.c9 = c9
                and   rs1.program_name = 'INTERNAL'
                AND   rs1.program_id=030911 
                AND (rs1.c6  IS NULL
                or rs1.c8  IS NULL
                or rs1.c9  IS NULL)
                and rs1.c28 IS NULL 
               )
          )  
WHERE program_name = 'INTERNAL'
AND program_id=030911
AND (c6  IS NULL
or c8  IS NULL
or c9  IS NULL)
and c28 IS NULL  

produces an error ORA-01427: single-row subquery returns more than one row. Which I think is what I want. I un-comment out -- and rs1.c6 = c6 and it tells me I updated 187 rows (which is right). I commit and then run a select on those 187 rows and there is nothing in c98.

Answer 1

The problem is that you need to deal with the entire sub-query as a single set in order to get row_number to give you the result you need, but you also need to reduce that set to a single record in order to update each row. While this is strictly possible with an update, it's a lot more straightforward to use a merge in this scenario (assuming you're using 10g):

MERGE INTO rptdata rp
USING      (SELECT ROW_NUMBER() OVER (PARTITION BY rs1.c1, 0, rs1.c5, rs1.c3 
                                      ORDER BY rs1.c1, rs1.c5, rs1.c3 DESC) AS r
            FROM   rptdata rs1
            WHERE  rs1.program_name = 'INTERNAL'
               AND rs1.program_id = 030911
               AND (rs1.c6 IS NULL OR rs1.c8 IS NULL OR rs1.c9 IS NULL)
               AND rs1.c28 IS NULL) rs
ON         (rs.c1 = rp.c1 AND rs.c5 = rp.c5 AND rs.c3 = rp.c3)
WHEN MATCHED THEN
   UPDATE SET
      c98   = rs.r
      WHERE      program_name = 'INTERNAL'
             AND program_id = 030911
             AND (c6 IS NULL OR c8 IS NULL OR c9 IS NULL)
             AND c28 IS NULL

---

Incidentally, the query you provided is not that far off. As stated above, you need to get the entire set with the row_number, then filter for the individual record you need for each row being updated. To do that, you need to move the join conditions to the outer query:

UPDATE rptdata
SET    c98   =
          (SELECT r
           FROM   (SELECT rs1.c1,
                          rs1.c5,
                          rs1.c3,
                          ROW_NUMBER() OVER (PARTITION BY rs1.c1, 0, rs1.c5, rs1.c3 
                                             ORDER BY rs1.c1, rs1.c5, rs1.c3 DESC)
                             AS r
                   FROM   rptdata rs1
                   WHERE  rs1.program_name = 'INTERNAL'
                      AND rs1.program_id = 030911
                      AND (rs1.c6 IS NULL OR rs1.c8 IS NULL OR rs1.c9 IS NULL)
                      AND rs1.c28 IS NULL)
           WHERE  rs1.c1 = c1 AND rs1.c5 = c5 AND rs1.c3 = c3)
WHERE  program_name = 'INTERNAL' 
   AND program_id = 030911 
   AND (c6 IS NULL OR c8 IS NULL OR c9 IS NULL) 
   AND c28 IS NULL