MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.36


CREATE TABLE TableA (
      a_id int primary key NOT NULL, 
      total_count int DEFAULT NULL, 
      pass_count int DEFAULT NULL, 
      fail_count int DEFAULT NULL
);

    INSERT INTO TableA 
    (a_id, total_count, pass_count, fail_count ) 
    VALUES 
    ( 1, 3, 2, 1 ),
	( 2, NULL, NULL, NULL ),
	( 3, 2, NULL, NULL ),
	( 4, NULL, NULL, NULL );

    SELECT * FROM TableA;

Records: 4  Duplicates: 0  Warnings: 0




a_id
total_count
pass_count
fail_count


1
3
2
1

2
null
null
null

3
2
null
null

4
null
null
null


    CREATE TABLE TableB (
        b_id INT primary key NOT NULL, 
        a_id INT NOT NULL, 
        isPassed varchar(20) DEFAULT ''
    );

    INSERT INTO TableB 
    (b_id, a_id, isPassed ) 
    VALUES 
    ( 1, 1,  'Y' ),
	( 2, 1,  'N' ),
	( 3, 1,  'Y' ),
	( 4, 2,  'N' ),
	( 5, 2,  'N' ),
	( 6, 2,  'Y' ),
	( 7, 2,  'N' ),
	( 8, 3,  'Y' ),
	( 9, 3,  'Y' ),
	( 10, 4, 'N' ),
	( 11, 4, 'N' ),
	( 12, 4, 'Y' );

    SELECT * FROM TableB;

Records: 12  Duplicates: 0  Warnings: 0




b_id
a_id
isPassed


1
1
Y

2
1
N

3
1
Y

4
2
N

5
2
N

6
2
Y

7
2
N

8
3
Y

9
3
Y

10
4
N

11
4
N

12
4
Y


UPDATE  TableA
SET     
    total_count = (SELECT COUNT(A_id) AS total_count 
                FROM TableB WHERE A_id = 2),
    pass_count = (SELECT COUNT(CASE WHEN(isPassed = 'Y') THEN 1 END) as pass_count 
                FROM TableB WHERE A_id = 2),
    fail_count = (SELECT COUNT(CASE WHEN(isPassed = 'N') THEN 1 END) as fail_count 
                FROM TableB WHERE A_id = 2)
WHERE   A_id = 2;


SELECT * FROM TableA;

Rows matched: 1  Changed: 1  Warnings: 0




a_id
total_count
pass_count
fail_count


1
3
2
1

2
4
1
3

3
2
null
null

4
null
null
null


WITH T0 AS (
    SELECT A_id,
        COUNT(*) AS total_count,
        SUM(isPassed = 'Y') AS pass_count,
        SUM(isPassed = 'N') AS fail_count
    FROM TableB
    GROUP BY A_id
)
UPDATE TableA a
INNER JOIN T0
   ON a.A_id = T0.A_id
SET     
    a.total_count = T0.total_count,
    a.pass_count = T0.pass_count,
    a.fail_count = T0.fail_count;

SELECT * FROM TableA;

Rows matched: 4  Changed: 2  Warnings: 0




a_id
total_count
pass_count
fail_count


1
3
2
1

2
4
1
3

3
2
2
0

4
3
1
2

a_id	total_count	pass_count	fail_count
1	3	2	1
2	null	null	null
3	2	null	null
4	null	null	null

a_id	total_count	pass_count	fail_count
1	3	2	1
2	4	1	3
3	2	null	null
4	null	null	null