CODEWITHSUNDEEP
sqloracle-databasegroup-bysubquery
Khandekar Mohammad Saleh
Khandekar Mohammad Saleh

Reputation: 120

UPDATE A table with subqueries with group by clause

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.

Upvotes: 0

Views: 379

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 1

user16351167
user16351167

Reputation:

Assuming this is MySQL...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(PK SERIAL PRIMARY KEY
,PK_PARENT INT NOT NULL
,ERROR VARCHAR(12) NULL
);

INSERT INTO my_table VALUES
(1,1,'ERR1'),
(2,1,NULL),
(3,1,NULL),
(4,1,NULL),
(5,1,NULL),
(6,2,'ERR'),
(7,2,'ERR'),
(8,2,'ERR');

SELECT pk_parent,SUM(error is null) ip_count FROM my_table GROUP BY pk_parent;
+-----------+----------+
| pk_parent | ip_count |
+-----------+----------+
|         1 |        4 |
|         2 |        0 |
+-----------+----------+

Upvotes: 1

Related Questions