主页 / coding / 问题 / 78940806
Accepted
ArtBindu
Asked: 2024-09-02 21:51:43 +0800 CST

根据另一个表更新 SQL 表的多个列

  • 772

我有两张桌子，TableA&TableB

所有表格数据均可在此处获取：db<>fiddle

TableA如下：
在此处输入图片描述

TableB包含以下通过/失败的场景：
在此处输入图片描述

我想要用特定tableA的数据更新。tableBA_id

我期望tableA输出如下：
更新表

因此，我编写了更新的查询A_id=2如下：

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

但是，它不起作用。你能帮我怎么做吗？
错误显示如下：
在此处输入图片描述

还有其他方法可以更新吗TableA？

注意：所有表格数据/查询都可以在这里找到：db<>fiddle

sql

2 个回答

  1. Best Answer

    您当前的更新存在多个问题，最大的问题是您似乎使用了 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;

    演示

    顺便说一句，鉴于中的数据TableA来自源TableB，您可能需要考虑仅基于T0cte 创建一个视图，然后在需要以这种方式查看数据时调用它。

    • 1

  2. 替代答案是：

    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;

    https://dbfiddle.uk/2y9O-TkK?highlight=2

    • 1

相关问题