hi guys i have this kind of problem concerning correlated update on oracle.
consider i have a table params.
id_s id_p value desc
-----------------------------------------------
10064 9 aaa r
10064 8 bbb t
10064 4 ccc t
10064 4 ttt y
11119 9 ddd f
11119 8 eee g
11119 4 fff b
11119 4 kkk x
so i want to update params to get the following
id_s id_p value desc
-----------------------------------------------
10064 9 aaa r
10064 8 bbb t
10064 4 ccc t
10064 4 ttt y
11119 9 aaa r
11119 8 bbb t
11119 4 ccc t
11119 4 ttt y
i write update like this
update params p1
set (p1.value, p1.desc) = (
select p2.value
, p2.desc
from params p2
where p2.id_s = 10064
and p2.id_p = p1.id_p
)
where p1.id_s = 11119
;
execution returns error 'ORA01427:Single-row subquery return more than one row'
how can i make this update work?
you have to add an additional condition to your subquery:
update params p1
set (p1.value, p1.desc) = (
select p2.value
, p2.desc
from params p2
where p2.id_s = 10064
and p2.id_p = p1.id_p
and p1.id_s = 11119
)
where p1.id_s = 11119
;
edit: matters are further complicated by the update of the op's spec, which effectively amounts to a partial pk update (as far as the table excerpts given are complete wrt their columns).
a possible solution implements the following basic idea: the result set for the master combination of id_p, id_s is paired with the result set of for the slave combination of the same columns according to the rank in 2 orderings. the orderings are simply the orderings of the result set according to the columns valueand desc, but of course any other would do as well (note that in particular 2 different orderings were feasible).
the said pairing is then correlated with the update resultset.
in oracle sql:
update params p1
set (p1.value, p1.desc) = (
select emb.value
, emb.desc
from (
select p2.value
, p2.desc
, p2.id_p
, rownum rn
from params p2
where p2.id_s = 10064
order by p2.value
, p2.desc
) emb
join (
select pm.value
, pm.desc
, pm.id_p
, rownum rn
from params pm
where pm.id_s = 11119
order by pm.value
, pm.desc
) emb_master
ON ( emb_master.id_p = emb.id_p
AND emb_master.rn = emb.rn )
where p1.id_s = 11119
and emb_master.id_p = p1.id_p
and emb_master.value = p1.value
and emb_master.desc = p1.desc
)
where p1.id_s = 11119
;
this scheme's viability hinges on the assumption that the result sets modulo each value of the tuple (id_p, id_s) be of the same cardibality. if thy don't, updates will be incomplete.
tested on ora 11g2 based on the excerpts given.