'Duplicate Entry', but would think ISNULL(Key) prevents that 1

[GKChesterton]

MySQL 5.0.27, PHP 5.

I am inserting records into a table that already contains records. I don't want duplicates, so there's a unique index. Good query design should let me eliminate potential duplicates; but, I am getting the error "Duplicate entry ... for Key 1."

INSERT INTO filter_cache 
( [COLOR=red]fid_part[/color], pn_oem, descr_oem, ) 
SELECT DISTINCT 
p.pid_part, p.pn_oem, p.descr 
FROM (part p LEFT JOIN filter_cache fc 
ON [COLOR=red]p.pid_part = fc.fid_part[/color]) 
INNER JOIN supplier_part_join sp 
ON p.pid_part = sp.fid_part 
WHERE [COLOR=red]ISNULL(fc.fid_part)[/color]
AND sp.pn_supplier LIKE '$search_str'

Shouldn't the ISNULL knock out the potential duplicates?

I'm new to MySQL, coming from MS Access. Maybe I'm having a syntax problem, perhaps with ISNULL. What's going wrong?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[r937]

INSERT IGNORE
  INTO filter_cache 
     ( fid_part, pn_oem, descr_oem, ) 
SELECT DISTINCT 
       p.pid_part, p.pn_oem, p.descr 
  FROM part p 
INNER 
  JOIN supplier_part_join sp 
    ON sp.fid_part = p.pid_part
   AND sp.pn_supplier LIKE '$search_str'

If you specify the IGNORE keyword in an INSERT statement, errors that occur while executing the statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the error is ignored and the row is not inserted.

r937.com | rudy.ca

 

[GKChesterton]

I am speechless with gratitude and devastated by the elegance of the solution. Many Thanks.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​