1

I am writing a SQL query to update a table from another table. My goal is for my query to insert records from table2 into table1 if that record is not found in table1.

The query works great when table1 is completely empty, but when I delete a couple records and try running my query it fails to re-insert the deleted values even though they are still in table2.

Here is my python code with the SQL query 

    cursor.execute('INSERT INTO table1 (col1, col2, col3, col4)  SELECT col1, col2, col3, col4 FROM table2 WHERE NOT EXISTS (SELECT col1, col2, col3, col4 FROM table1)')

When I delete a record in Access from table1 and try re running the script it does nothing, but when I delete everything from table1 and re run the script it works perfectly.

Gpsy
  • 100
  • 11

1 Answers1

2

This is how NOT EXISTS work, assuming col1 is primary key in the tables

INSERT INTO table1 (col1, col2, col3, col4)  
SELECT col1, col2, col3, col4 
FROM table2  t2
WHERE NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t1.col1 = t2.col1)
Serg
  • 22,285
  • 5
  • 21
  • 48