UPDATE A table with subqueries with group by clause

Question

I have two table with below structure

create table PARENT(
  pk varchar(255) not null, 
  ip_count int, 
  primary key(pk)
);

create table CHILD_INPUT(
  pk varchar(255) not null, 
  pk_parent varchar(255), 
  error varchar(255), 
  primary key(pk)
);

CHILD_INPUT table has foreign key "pk_parent" which references "pk" column of "PARENT" table.

Below are some sample data:

PARENT TABLE:

PK	IP_COUNT
PK0001	NULL
PK0002	NULL

CHILD_INPUT

PK	PK_PARENT	ERROR
CPK001	PK0001	ERR1
CPK002	PK0001	NULL
CPK003	PK0001	NULL
CPK004	PK0001	NULL
CPK005	PK0001	NULL
CPK006	PK0002	ERR
CPK007	PK0002	ERR
CPK008	PK0002	ERR

I need to write a update query where I have to update the "ip_count" of the PARENT table with the count of child records provided the "ERROR" column is NULL i.e the expected output should be like below:

PK	IP_COUNT
PK0001	4
PK0002	0

Explanation: PK0001 has 4 records in child table with error column set to NULL. PK0002 has no records in child table with error set to NULL.

Answer 1

If it is Oracle, then merge might help.

SQL> MERGE INTO parent p
  2       USING (  SELECT c.pk_parent,
  3                       SUM (CASE WHEN error IS NULL THEN 1 ELSE 0 END) cnt
  4                  FROM child_input c
  5              GROUP BY c.pk_parent) x
  6          ON (p.pk = x.pk_parent)
  7  WHEN MATCHED
  8  THEN
  9     UPDATE SET p.ip_count = x.cnt;

2 rows merged.

SQL> SELECT * FROM parent;

PK           IP_COUNT
---------- ----------
PK0001              4
PK0002              0

SQL>

---

[EDIT] If there are no child records for some parents, then UPDATE instead of MERGE:

SQL> select * from parent order by pk;

PK           IP_COUNT
---------- ----------
PK0001
PK0002
PK0003                     --> no child records for this parent

SQL> select * from child_input order by pk_parent;

PK         PK_PARENT  ERROR
---------- ---------- ----------
CPK001     PK0001     ERR1
CPK002     PK0001
CPK003     PK0001
CPK004     PK0001
CPK005     PK0001
CPK006     PK0002     ERR
CPK007     PK0002     ERR
CPK008     PK0002     ERR

8 rows selected.

Update:

SQL> UPDATE parent p
  2     SET p.ip_count =
  3            (SELECT NVL (SUM (CASE WHEN error IS NULL THEN 1 ELSE 0 END), 0) cnt
  4               FROM child_input c
  5              WHERE c.pk_parent = p.pk);

3 rows updated.

Result:

SQL> select * from parent order by pk;

PK           IP_COUNT
---------- ----------
PK0001              4
PK0002              0
PK0003              0

SQL>