Using row_number() in subquery results in ORA-00913: too many values

Question

In Oracle, I wish to do something like the SQL below. For each row in "criteria," I want to find the latest row in another table (by last_modified_date) for the same location_id, and use that value to set default_start_interval. Or, if there is no such value, then use 30. However, as you can see, the subquery must have two values in the select statement to use row_number(). That causes an error. How do I reformat it so that it works?

update criteria pc set default_start_interval = 
    COALESCE(
        (SELECT start_interval, 
                row_number() over(partition by aday.location_id 
                                  order by atime.last_modified_date desc
                             ) as rn
            FROM available_time atime 
            JOIN available_day aday ON aday.available_day_id = atime.available_day_id
            WHERE aday.location_id = pc.location_id
                and rn = 1)
        , 30)

Answer 1

There are two issues in your update query:

1. The update expects only one value per row for default_start_interval, however, you have two columns in the select list.

2. The row number should be assigned before in the inner query, and then apply filter where rn = 1 in outer query.

Your update query should look like:

UPDATE criteria pc
SET default_start_interval = NVL(
    (
    SELECT start_interval FROM(
        SELECT
            start_interval, ROW_NUMBER() OVER(
                PARTITION BY aday.location_id 
                ORDER BY atime.last_modified_date DESC
            ) AS rn
        FROM
            available_time atime
            JOIN available_day aday ON aday.available_day_id = atime.available_day_id
        WHERE
            aday.location_id = pc.location_id
        )
    WHERE rn = 1)
    , 30)

Note: You could simply use NVL instead of COALESCE as you only have one value to check for NULL. COALESCE is useful when you have multiple expressions.