1

I'm trying to fix an incomplete database where I don't have control over the schema and I need to find missing entries and insert them.

This is syntax I've arrived at:

INSERT INTO 
  downloads (product_id, filename)
VALUES 
( 
  products_id = ( SELECT id
         FROM products 
         WHERE id NOT IN
           ( SELECT product_id
             FROM downloads
           )
       ), 
  filename = 'default.zip'
)

The subquery works fine alone but the above throws an error

#1093 - You can't specify target table 'download' for update in FROM clause

I've reviewed many of the StackOverflow questions related to this error (like this), and I gather that there is a way to nest a subquery such that it "creates an implicit temporary table, so it doesn't count as the same table you're updating" but I haven't been able to adapt it.

If my approach is inefficient that's OK, I'm very curious to see a solution that get's close to my syntax but any solution would be helpful.

Community
  • 1
  • 1
Mark Fox
  • 8,694
  • 9
  • 53
  • 75

1 Answers1

2

Things to do,

  • use INSERT...INTO SELECT statement
  • use LEFT JOIN instead of NOT IN
  • don't forget to add index on this columns: products.ID and downloads.product_id for faster performance

query,

INSERT INTO  downloads (product_id, filename)
SELECT  a.id AS product_id, 'default.zip' AS filename
FROM    products a
        LEFT JOIN downloads b
            ON a.ID = b.product_id
WHERE   b.product_id IS NULL
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Worked like a charm. My lack of understanding about the meaning of `IS NULL` lead me to the nested subquery solution. – Mark Fox Jan 17 '13 at 00:38