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

sql ms-access insert not-exists
bonzo46 picture bonzo46 · Feb 3, 2010 · Viewed 50.8k times · Source

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

Answer

Tor Valamo picture Tor Valamo · Feb 3, 2010

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

Related questions

How to create a new column in a select query

In MS Access, I want to insert a new column into the returned result of a select query. The new column has the same value for every row. For example, my select returns columns A, B and I want C …

Read More
Using Autonumbering in Access - INSERT statements

I'm having trouble running an INSERT statement where there's an autonumber as the PK field. I have an Auto-incrementing long as the Primary Key, and then 4 fields of type double; and yet Access (using ADO) seems to want five values …

Read More
How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More