Insert record into table if entry does not exist in another table- with an extra twist

Question

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

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:

[table1] file_index : filename

[table2] 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'

With that selection I then want to insert a new record into [table2]

[file_index] = [table1].[file_index] [celeb_name] = 'Audrey Hepburn'

There is a one to many relationship between [file_index] in [table1] and [table2] One record in [table1], to many in [table2].

Many thanks

Westfall

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

Color

Tor - you truly are a mighty SQL god, as your name suggests. Thank you so much. It worked – Westfall

NOT IN is not well-optimized in Jet/ACE, as it will often not use the indexes on both sides (or on either side in some cases). And OUTER JOIN is going to much more reliable, as it always uses indexes if the joined fields are indexed. – Suzettesuzi

@Suzettesuzi - Anyone who uses Access and expects performance should really look elsewhere... ;) – Color

@Suzettesuzi - What I mean is, you use Access for desktop apps, not for a server cluster with petabytes of data. And so any 4 ms delay caused by this subquery is never and will never be a problem. Note also that Access has a 2 GB database size limit, so I don't see much of a scenario where things will take a while. – Color

This query is dope. Helped get me where I needed to be. Thanks. – Consequential

Answer 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:

  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)

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

Suzettesuzi

Answer 3

You can use NOT Exists

I think it is the best way from the side of performance.

As Follow:

INSERT INTO table2 (file_index, celeb_name)
SELECT file_index, 'Audrey Hepburn'
FROM table1
WHERE filename = 'aud'
  AND NOT Exists (SELECT file_index 
                         FROM table2 
                         WHERE celeb_name = 'Audrey Hepburn')

Erode

Answer 4

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!!

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

Westfall

Sorry, my initial edit was completely wrong, just careless translation on my part. I just fixed it up and replaced my query with the above. – Westfall