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;
并检查 TableA 其更新数据是否正确。
但是,上述查询对于每个列更新都有 3 个不同的子查询。因此,我想编写更新后的查询,如下所示:
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
您当前的更新存在多个问题,最大的问题是您似乎使用了 SQL Server 的更新连接语法。MySQL 更新连接语法首先执行连接,然后执行集合。此外,您使用的别名不一致,并且存在问题。以下版本按预期运行。
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;
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;