gpt4 book ai didi

sql - 如果记录存在于目标中但不存在于源中,我需要删除它

转载 作者:行者123 更新时间:2023-12-04 17:38:58 25 4
gpt4 key购买 nike

所以,我有两个表,目标表和源表。我需要删除目标表中存在但源表中不存在的行。

还有代码:

MERGE INTO (SELECT id_car_bk, car_brand_bk, car_type_bk, new_car
FROM car_catalog_backup) CB
USING (SELECT id_car, car_brand, car_type FROM car_catalog) C
ON (CB.id_car_bk = b.id_car)
WHEN NOT MATCHED THEN
INSERT
(CB.id_car_bk, CB.car_brand_bk, CB.car_type_bk)
VALUES
(C.id_car, C.car_brand, C.car_type)
WHEN MATCHED THEN
UPDATE SET CB.car_brand_bk = C.car_brand;

最佳答案

你可以使用

DELETE car_catalog_backup b 
WHERE not exists
( SELECT 0
FROM car_catalog c
WHERE b.id_car_bk = c.id_car );

DELETE car_catalog_backup b 
WHERE b.id_car_bk not in
( SELECT c.id_car
FROM car_catalog c );

假设 car_catalogcar_catalog_backup目标。第一个更好,因为它的性能更高。

如果您的目标是使用与您的情况类似的 MERGE 语句找出答案,则使用以下

MERGE INTO car_catalog_backup a
USING (SELECT id_car, car_brand, car_type, car_brand_bk
FROM car_catalog
JOIN car_catalog_backup
ON id_car_bk = id_car
) b
ON (a.id_car_bk = b.id_car)
WHEN MATCHED THEN
UPDATE SET a.new_car = 1
DELETE
WHERE a.car_brand_bk != b.car_brand
WHEN NOT MATCHED THEN
INSERT
(id_car_bk, car_brand_bk, car_type_bk)
VALUES
(b.id_car, b.car_brand, b.car_type)

删除匹配id列(a.id_car_bk = b.id_car)但不匹配brand code列(a.car_brand_bk != car_brand ) 为例。

Demo

关于sql - 如果记录存在于目标中但不存在于源中,我需要删除它,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55393130/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com