Hi,
easiest way (not the most optimal) is adding exists condition
update t_TNC t
set (Ref_CT) =
(
select count (r.Cited_Patent_NR) as Ref_CT
from t_Citation r
where r.Patent_ID = t.Patent_ID
group by r.Patent_ID
)
-- add this
WHERE EXISTS (
select 1
from t_Citation r
where r.Patent_ID = t.Patent_ID
)
;
alternative I use only with small number of second subquery results is this:
update t_TNC t
set (Ref_CT) =
(
select count (r.Cited_Patent_NR) as Ref_CT
from t_Citation r
where r.Patent_ID = t.Patent_ID
group by r.Patent_ID
)
-- add this
WHERE t.Patent_ID IN (
select r.Patent_ID
from t_Citation r
)
;
as you see Oracle needs more coding but it offers more straight logic -
that you cannot specify which rows to update when you specify which
values to set to chosen columns ( in SET section), you just have to also
specify WHERE to SET the values (in WHERE section)
regards
hoppo
On 10. 1. 2012 22:39, Dom wrote:
update t_TNC t
set (Ref_CT) =
(
select count (r.Cited_Patent_NR) as Ref_CT
from t_Citation r
where r.Patent_ID = t.Patent_ID
group by r.Patent_ID
)
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
