oracle - "Single-row subquery returns more than one row" in Update statement -
i've been struggling couple of days work out how make update
statement work. select
statement appears work expected, when try update column values ora-01427 single-row subquery returns more 1 row
error.
here's code:
update tbl_metrics set act_end_time = (with base (select caseid, entry_timestamp activity act_id in (100, 700, 300) ) select t1.entry_timestamp base t1, tbl_metrics t2 t1.caseid = t2.caseid , t2.act_start_time < (select min(t1.entry_timestamp) base t1 t1.caseid = t2.caseid))
the idea tbl_metrics.act_end_time
column updated lowest entry_timestamp
value activity
table activity.caseid=tbl_metrics.caseid
, activity.entry_timestamp>tbl_metrics.act_start_time
, activity.act_id
100, 700, or 300.
i think should this:
update tbl_metrics t2 set act_end_time = (select min(t1.entry_timestamp) activity t1 act_id in (100, 700, 300) , t1.entry_timestamp > t2.act_start_time , t1.caseid = t2.caseid)
Comments
Post a Comment