我已经挣扎了几天,以弄清楚如何使我的update
语句起作用。select
语句似乎按预期工作,但是当我尝试更新列值时,出现ORA-01427 single-row subquery returns more than one row
错误。
代码如下:
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))
这个想法是,tbl_metrics.act_end_time
列使用表中的最低entry_timestamp
值进行更新activity
其中activity.caseid=tbl_metrics.caseid
和activity.entry_timestamp>tbl_metrics.act_start_time
,activity.act_id
为 100、700 或 300。
我认为应该是这样的:
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)