插入多行，但对于每行检查是否尚不存在

发布于 2025-02-13 00:18:49 字数 555 浏览 1 评论 0原文

如果目标表中存在，我希望插入多行。但是我不确定如何处理以下代码：

INSERT INTO sales.promotions(
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
('2019 Summer Promotion', 0.15, '20190601', '20190901'),
('2019 Fall Promotion',   0.20, '20191001', '20191101'),
('2019 Winter Promotion', 0.25, '20191201', '20200101');

因为不存在条款将适用于所有行，而我需要行进行行：

WHERE NOT EXISTS (SELECT * FROM sales.promotions
    WHERE promotion_name = 'Winter Promotion');

对不起，如果这很明显，不是太擅长SQL，我不确定如何为适当的研究介绍这个问题。

原文

I am looking to insert multiple rows if they don't EXIST in the target table. But I'm not sure how do this with the following code:

INSERT INTO sales.promotions(
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
('2019 Summer Promotion', 0.15, '20190601', '20190901'),
('2019 Fall Promotion',   0.20, '20191001', '20191101'),
('2019 Winter Promotion', 0.25, '20191201', '20200101');

Because the WHERE NOT EXIST clause would apply to ALL rows whereas I need to do it row by row:

WHERE NOT EXISTS (SELECT * FROM sales.promotions
    WHERE promotion_name = 'Winter Promotion');

Sorry if this is painfully obvious, not too good at SQL and I'm not really sure how to word this question for proper research.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨落画卷 2025-02-20 00:18:49

可以使用表值构造函数带有的存在：

INSERT INTO sales.promotions(promotion_name, discount, start_date, expired_date)
SELECT *
FROM (VALUES
    ('2019 Summer Promotion', 0.15, '20190601', '20190901'),
    ('2019 Fall Promotion',   0.20, '20191001', '20191101'),
    ('2019 Winter Promotion', 0.25, '20191201', '20200101')
) AS values_tobe_inserted(promotion_name, discount, start_date, expired_date)
WHERE NOT EXISTS (
    SELECT *
    FROM sales.promotions
    WHERE sales.promotions.promotion_name = values_tobe_inserted.promotion_name
)

It is possible to use table value constructor with exists:

INSERT INTO sales.promotions(promotion_name, discount, start_date, expired_date)
SELECT *
FROM (VALUES
    ('2019 Summer Promotion', 0.15, '20190601', '20190901'),
    ('2019 Fall Promotion',   0.20, '20191001', '20191101'),
    ('2019 Winter Promotion', 0.25, '20191201', '20200101')
) AS values_tobe_inserted(promotion_name, discount, start_date, expired_date)
WHERE NOT EXISTS (
    SELECT *
    FROM sales.promotions
    WHERE sales.promotions.promotion_name = values_tobe_inserted.promotion_name
)

回复收藏 0 原文

~没有更多了~