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
-- INNER JOIN (
--     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
--     -- HAVING A_id = 3
-- ) T0
--    ON TableA.A_id = T0.A_id
-- SET     
--     TableA.total_count = T0.total_count,
--     TableA.pass_count = T0.pass_count,
--     TableA.fail_count = T0.fail_count
-- WHERE TableA.A_id = T0.A_id;

-- SELECT * FROM TableA;
UPDATE TableA
JOIN (
    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
    -- HAVING A_id = 3
) T0
   ON TableA.A_id = T0.A_id
SET     
    TableA.total_count = T0.total_count,
    TableA.pass_count = T0.pass_count,
    TableA.fail_count = T0.fail_count
WHERE TableA.A_id = T0.A_id;

SELECT * FROM TableA;

Rows matched: 4  Changed: 3  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


-- MERGE INTO TableA USING (
--     SELECT A_id, 
--            COUNT(A_id) AS total_count, 
--            SUM(CASE WHEN isPassed = 'Y' THEN 1 ELSE 0 END) AS pass_count, 
--            SUM(CASE WHEN isPassed = 'N' THEN 1 ELSE 0 END) AS fail_count 
--     FROM TableB 
--     GROUP BY A_id
-- ) T0 
-- ON (TableA.A_id = T0.A_id)
-- WHEN MATCHED THEN 
-- UPDATE SET 
--     TableA.total_count = T0.total_count, 
--     TableA.pass_count = T0.pass_count, 
--     TableA.fail_count = T0.fail_count;

-- SELECT * FROM TableA;
a_id	total_count	pass_count	fail_count
1	3	2	1
2	null	null	null
3	2	null	null
4	null	null	null