如果条目不存在于另一个表中，则将记录插入到表中，并添加一个额外的扭转。

Hi to all you mighty SQLsuperheros out there.. Can anyone rescue me from imminent disaster and ruin?

大家好，我是伟大的SQLsuperheros。谁能把我从即将来临的灾难和毁灭中拯救出来?

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:

我正在使用微软Access SQL。我想在一个表(表1)中选择另一个表中没有的记录(表2)。然后将基于表1中的记录的新记录插入表2，如下所示:

[table1] file_index : filename

(表1)file_index:文件名

[table2] file_index : celeb_name

(表二)file_index:celeb_name

I want to:

我想:

Select all records from table1 where [filename] is like aud and whose corresponding [file_index] value does not exist in table2 with with field [celeb_name] = 'Audrey Hepburn'

从表1中选择所有的记录，其中[文件名]类似aud，其对应的[file_index]值在表2中与字段[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]=(表1)。[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].

[表1]中的[file_index]与[表2]中的一条记录与[表1]中的许多记录之间存在一到多个关系。

Many thanks

非常感谢

3 个解决方案

#1

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')

#2

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:

正如我在评论中所说，NOT in不是Jet/ACE优化得很好，使用外部连接通常更有效。在这种情况下，因为您需要在连接的外部进行筛选，您需要一个子查询:

  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很糟糕，尤其是在使用连接语法的时候)

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).

但是我必须说，我想知道这是否会插入太多的记录(同样的保留适用于非IN版本)。

#3

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*"));

#1