Hmmmmm. Confusing to this MS Sql programmer. It looks like "count (r.Cited_patent_NR)" without the group by should return just the count of the number of records in the t_citation file. The "Merge into" statement looks better. Does it take longer to run?
On Jan 10, 8:20 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > 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 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
