SQL 如果条目不存在于另一个表中，则将记录插入表中 - 有额外的扭曲
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2192560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert record into table if entry does not exist in another table- with an extra twist
提问by bonzo46
Hi to all you mighty SQLsuperheros out there.. Can anyone rescue me from imminent disaster and ruin?
向所有强大的 SQL 超级英雄致敬。
I'm working with Microsoft Access SQL. I'd like to select records in one table (table1) that don't appear in another (table2) .. and then insert new records into table2 that are based on records in table1, as follows:
我正在使用 Microsoft Access SQL。我想选择一个表 (table1) 中没有出现在另一个 (table2) 中的记录..然后将基于 table1 中记录的新记录插入 table2，如下所示：
[table1] file_index : filename
[table1] file_index : 文件名
[table2] file_index : celeb_name
[table2] file_index : 名人名
I want to:
我想要：
Select all records from table1 where [filename] is like audand whose corresponding [file_index] value does not exist in table2 with with field [celeb_name] = 'Audrey Hepburn'
从 table1 中选择所有记录，其中 [filename] 类似于aud并且其对应的 [file_index] 值在 table2 中不存在，字段为 [celeb_name] = 'Audrey Hepburn'
With that selection I then want to insert a new record into [table2]
通过该选择，我想在 [table2] 中插入一条新记录
[file_index] = [table1].[file_index] [celeb_name] = 'Audrey Hepburn'
[file_index] = [table1].[file_index] [celeb_name] = '奥黛丽赫本'
There is a one to many relationship between [file_index] in [table1] and [table2] One record in [table1], to many in [table2].
[table1]中的[file_index]与[table2]之间存在一对多关系[table1]中的一条记录，[table2]中的多条记录。
Many thanks
非常感谢
采纳答案by Tor Valamo
Will this do? Obviously add some square brackets and stuff. Not too into Access myself.
这会吗？显然添加了一些方括号之类的东西。我自己不太喜欢访问。
INSERT INTO table2 (file_index, celeb_name)
SELECT file_index, 'Audrey Hepburn'
FROM table1
WHERE filename = 'aud'
AND file_index NOT IN (SELECT DISTINCT file_index
FROM table2
WHERE celeb_name = 'Audrey Hepburn')
回答by David-W-Fenton
As I said in comments, NOT IN is not well-optimized by Jet/ACE and it's usually more efficient to use an OUTER JOIN. In this case, because you need to filter on the outer side of the join, you'll need a subquery:
正如我在评论中所说，Jet/ACE 没有很好地优化 NOT IN，使用 OUTER JOIN 通常更有效。在这种情况下，因为您需要在连接的外侧进行过滤，您将需要一个子查询：
INSERT INTO photos_by_celebrity ( ORIG_FILE_INDEX, celebrity_name )
SELECT tblOriginal_Files.ORIG_FILE_INDEX, 'Audrey Hepburn'
FROM tblOriginal_Files
LEFT JOIN (SELECT DISTINCT ORIG_FILE_INDEX
FROM photos_by_celebrity
WHERE celebrity_name = 'Audrey Hepburn') AS Photos
ON tblOriginal_Files.ORIG_FILE_INDEX = Photos.ORIG_FILE_INDEX
WHERE Photos.ORIG_FILE_INDEX Is Null;
(that may not be exactly right -- I'm terrible with writing SQL by hand, particularly getting the JOIN syntax right)
（这可能不完全正确——我手工编写 SQL 很糟糕，尤其是正确使用 JOIN 语法）
I must say, though, that I'm wondering if this will insert too many records (and the same reservation applies to the NOT IN version).
不过，我必须说，我想知道这是否会插入太多记录（同样的保留适用于 NOT IN 版本）。
回答by bonzo46
In the original question I'd modified my table and field names and inserted square brackets in to make it easier to read.
在最初的问题中，我修改了我的表和字段名称，并在其中插入了方括号以使其更易于阅读。
Below is the final SQL statement that worked in MS Access format. Awesome result, thanks again Tor!!
以下是以 MS Access 格式运行的最终 SQL 语句。很棒的结果，再次感谢 Tor！
INSERT INTO photos_by_celebrity ( ORIG_FILE_INDEX, celebrity_name )
SELECT tblOriginal_Files.ORIG_FILE_INDEX, 'Audrey Hepburn' AS Expr1
FROM tblOriginal_Files
WHERE (((tblOriginal_Files.ORIG_FILE_INDEX) Not In (SELECT DISTINCT ORIG_FILE_INDEX
FROM photos_by_celebrity
WHERE celebrity_name = 'Audrey Hepburn')) AND ((tblOriginal_Files.ORIGINAL_FILE) Like "*aud*"));
