DELETE records resulting from JOIN query? 2

[admoore]

I am attempting to eliminate duplicate records being imported from a table called "import" that already exist in a target table, AND in this particular case, I can not use a primary key to prevent this behavior.

I have a join query that correctly identifies the records in the import table which I wish to eliminate before importing the remaining records.

SELECT bonus_import.vin
FROM bonus_import
INNER JOIN bonus ON bonus_import.vin = bonus.vin
AND bonus_import.post_date = bonus.post_date

This fails:

DELETE FROM bonus_import
SELECT bonus_import.*
FROM bonus_import
INNER JOIN bonus ON bonus_import.vin = bonus.vin
AND bonus_import.post_date = bonus.post_date

I suppose I would be happy as well altering my INSERT query to only bring in the records excluded from the join...

TIA,

-Allen M.

 

[r937]

try this (after making a backup, of course) --

DELETE bonus_import
  FROM bonus
INNER 
  JOIN bonus_import
    ON bonus_import.vin = bonus.vin
   AND bonus_import.post_date = bonus.post_date

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[admoore]

Perfect. And thanks.

p.s. Simply SQL is a great resource.

-A

 

[r937]

thanks

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon