Your COUNT function is returning null because of your group by.
try this
set echo on
drop table t_tnc;
drop table t_citation;
create table t_tnc as
select 1 patent_id, 0 ref_ct from dual union all
select 4, 0 from dual union all
select 6, 0 from dual;
create table t_citation as
select 1 patent_id, 91 cited_patent_nr from dual union all
select 1 , 92 cited_patent_nr from dual union all
select 1 , null cited_patent_nr from dual union all
select 2 , 91 cited_patent_nr from dual union all
select 4 , null cited_patent_nr from dual union all
select 4 , 91 cited_patent_nr from dual;
SELECT * from t_tnc;
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
);
SELECT * from t_tnc;
rollback;
================================================
SQL> SELECT * from t_tnc
PATENT_ID REF_CT
---------- ----------
1 2
4 1
6 0
3 rows selected.
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
