我想编写一个查询来插入动态值(如果它们已经不存在)。到目前为止,我已经尝试过了。
INSERT MTB_AML..tb_aml_codes (aml_code, aml_desc)
SELECT 'NRA', 'Non-Resident Alien'
UNION
SELECT 'DOM', 'Resident Alien'
FROM MTB_AML..tb_aml_codes t1
WHERE t1.aml_code NOT IN (SELECT t2.aml_code from MTB_AML..tb_aml_codes t2)
但这仅返回上选择(已存在于表中)。我究竟做错了什么?
我认为您遇到的问题是,在您的版本中,WHERE子句仅适用于UNION子句中的最后一个Select。
记录您想要的记录
SELECT 'NRA' AS aml_code, 'Non-Resident Alien' AS aml_desc
UNION
SELECT 'DOM' AS aml_code, 'Resident Alien' AS aml_desc
然后将它们包装为一个子查询(在示例中别名为[src]),然后可以检查目标表中哪些键没有匹配的键(别名为[dst])
INSERT MTB_AML..tb_aml_codes (aml_code, aml_desc)
SELECT src.aml_code, src.aml_desc
(
SELECT 'NRA' AS aml_code, 'Non-Resident Alien' AS aml_desc
UNION
SELECT 'DOM' AS aml_code, 'Resident Alien' AS aml_desc
) src
WHERE src.aml_code NOT IN (SELECT dst.aml_code from MTB_AML..tb_aml_codes dst)
就我个人而言,我会像这样通过左连接来完成此操作,但这取决于您
INSERT MTB_AML..tb_aml_codes (aml_code, aml_desc)
SELECT src.aml_code, src.aml_desc
FROM
(
SELECT 'NRA' AS aml_code, 'Non-Resident Alien' AS aml_desc
UNION
SELECT 'DOM' AS aml_code, 'Resident Alien' AS aml_desc
) src
LEFT JOIN MTB_AML..tb_aml_codes dst
ON dst.aml_code = src.aml_code
WHERE dst.aml_code IS NULL
两者都可以,但是如果您必须在多列键上进行匹配,则需要使用join方法
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句