update子句

5f0d552i 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(247)

我有一个查询，显示协议和协议状态，其中只有一个参与者，并且协议已打开：

SELECT a.protocol_number, b.STATUS_NAME, COUNT(*) FROM PARTICIPANTS a LEFT JOIN PROTOCOLS b ON a.PROTOCOL_NUMBER = b.PROTOCOL_NUMBER GROUP BY a.PROTOCOL_NUMBER, b.STATUS_NAME HAVING count(*) = 1 AND b.STATUS_NAME = 'OPEN';

我想将这些协议的状态更新为closed，但找不到运行正常的查询。我试过这个但它永远挂着：

UPDATE
    PROTOCOLS p1
SET
    p1.STATUS_NAME = 'CLOSED'
WHERE
    p1.protocol_Number IN (
    SELECT
        PROTOCOL_NUMBER
    FROM
        (
        SELECT
            a.protocol_number, b.STATUS_NAME, COUNT(*)
        FROM
            PARTICIPANTS a
        LEFT JOIN PROTOCOLS b ON
            a.PROTOCOL_NUMBER = b.PROTOCOL_NUMBER
        GROUP BY
            a.PROTOCOL_NUMBER, b.STATUS_NAME
        HAVING
            count(*) = 1
            AND b.STATUS_NAME = 'OPEN');

sql Ignite

来源：https://stackoverflow.com/questions/63318847/update-clause-with-having-count

2条答案

按热度按时间

kxkpmulp1#

假设状态名称“open”只分配给一个 PROTOCOL_NUMBER .

UPDATE
    PROTOCOLS p1
SET
    p1.STATUS_NAME = 'CLOSED'
WHERE
    STATUS_NAME = 'OPEN'
    and not exists (select null
                from protocols p2
                where p2.PROTOCOL_NUMBER = p1.PROTOCOL_NUMBER
                 and b.STATUS_NAME <> 'OPEN');

赞(0）回复(0）举报 2021-07-24

b09cbbtk2#

使用update查询，您不需要再次连接子查询中的protocol表，使用exists我们可以将它与外部查询关联起来。
假设你有索引 protocol_number 下面应该做这项工作。你能试试下面的吗，

update protocols p1
   set p1.status_name = 'CLOSED'
 where p1.status_name = 'OPEN'
   and exists (select 1
                 from participants a
                where a.protocol_number = b.protocol_number
                group by a.protocol_number
                having count(*) = 1)

赞(0）回复(0）举报 2021-07-24

我来回答

update子句

2条答案

相关问题

热门标签

最新问答