主页 / 问题 / 1543609
Accepted
pra_soul_owl
Asked:2023-10-02 16:04:18 +0000 UTC2023-10-02 16:04:18 +0000 UTC

SET UPDATE，其中 WHERE 由一组相关的多列指定

  • 772

有一个表，SECURITY_EXCHANGE其中包含列security_id(int)、is_here(boolean)、market_id(int)

对价值观security_id和market_id独特性。

我需要将一系列值更新is_here为null。如果我提出这样的请求，那么它就会起作用。

UPDATE SECURITY_EXCHANGE
SET is_here = null
WHERE security_id in (1,2,3,4,5);

但我需要WHERE考虑关联列中的值security_id+ market_id。

也就是说，像这样：

UPDATE SECURITY_EXCHANGE
    SET is_here = null
    WHERE security_id AND market_id in ...;

如何实施？

sql

1 个回答

  1. Best Answer

    正如@Akina 提到的。

    这是使用表值构造函数的完整演示。

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, security_id INT, market_id INT, is_here BIT DEFAULT(1));
INSERT @tbl (security_id, market_id) VALUES
(1, 2),
(1, 29),
(1, 18),
(1, 3);
-- DDL and sample data population, end

-- просто чтобы увидеть
SELECT t.* 
FROM @tbl AS t INNER JOIN
    (VALUES
          (1, 29),
          (1, 18)
) AS Params(security_id, market_id)
    ON t.security_id = Params.security_id
        AND t.market_id = Params.market_id;

-- фактическое действие
UPDATE t
SET is_here = null
FROM @tbl AS t INNER JOIN
    (VALUES
          (1, 29),
          (1, 18)
) AS Params(security_id, market_id)
    ON t.security_id = Params.security_id
        AND t.market_id = Params.market_id;

-- тест
SELECT * FROM @tbl;

    结果

    ID 安全 ID 市场ID 在这儿
    1 1 2 1
    2 1 29 无效的
    3 1 18 无效的
    4 1 3 1
    • 2

相关问题