“Single-row subquery returns more than one row” in Update statement

Home / Uncategorized / “Single-row subquery returns more than one row” in Update statement

Question:
I’ve been struggling for a couple of days to work out how to make my update statement work. The select statement appears to work as expected, but when I try to update my column values I get an ORA-01427 single-row subquery returns more than one row error.

Here’s the code:UPDATE tbl_metrics
SET act_end_time = (WITH base
AS (SELECT caseid, entry_timestamp
FROM activity
WHERE act_id IN (100, 700, 300)
)
SELECT t1.entry_timestamp
FROM base t1, tbl_metrics t2
WHERE t1.caseid = t2.caseid
AND t2.act_start_time < (SELECT MIN(t1.entry_timestamp) FROM base t1 WHERE t1.caseid = t2.caseid)) The idea is that the tbl_metrics.act_end_time column is updated with the lowest entry_timestamp value from activity table where activity.caseid=tbl_metrics.caseid and activity.entry_timestamp>tbl_metrics.act_start_time and the activity.act_id is 100, 700, or 300.


Answer:
I think it should be like this:UPDATE tbl_metrics t2
SET act_end_time =
(SELECT MIN(t1.entry_timestamp)
FROM activity t1
WHERE act_id IN (100, 700, 300)
AND t1.entry_timestamp > t2.act_start_time
AND t1.caseid = t2.caseid)
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *