Search code examples
sqlmysql

Update multiple column of a SQL table based on another table

I have two tables, TableA & TableB

All tables data are available here: db<>fiddle

TableA as follows:
enter image description here

TableB contains the passed/failed scenario as follows:
enter image description here

I want to updating tableA with data of tableB for particular A_id.

I'm expecting tableA output as:
updated_table

So, I have write updated query for A_id=2 as follows:

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;

And checked the TableA it's updating data properly.

But, above query have 3 different sub-query for each column update.
So, I want to write updated query as follows:

WITH T0 AS (
    SELECT A_id,
        COUNT(A_id) AS TOTAL_SCENARIOS_COUNT,
        COUNT(CASE WHEN(isPassed = 'Y') THEN 1 END) as PASS_SCENARIOS_COUNT,
        COUNT(CASE WHEN(isPassed = 'N') THEN 1 END) as FAIL_SCENARIOS_COUNT
    from RT_TEST_RUN_SCENARIO
    GROUP BY A_id    
    HAVING A_id = 4779
)
UPDATE  tableA
SET     
    tableA.total_count = t0.total_count,
    tableA.pass_count = t0.pass_count,
    tableA.fail_count = t0.fail_count
FROM tableA INNER JOIN T0
ON tableA.A_id = T0.A_id

But, it's not working. Can you help me how to do that ?
Error is showing as follows:
enter image description here

Is there other way to update TableA ?

N.B: All tables data/query are available here: db<>fiddle

Solution

  • Your current update has multiple problems, the biggest being that you seem to be using SQL Server's update join syntax. The MySQL update join syntax does the join first, followed by the set. Also, the aliases you used were inconsistent and had problems. The following version is working as you expected.

    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;

    Demo

    By the way, given that the data in TableA is derived from the source TableB, you might want to consider just creating a view based on your T0 cte and then calling it whenever you need to see the data this way.