[thelist] SQL: Insert with FK subselect?

[Mattias Thorslund]

On 06/15/2011 10:34 AM, Francis Marion wrote:
> This is returning an (unspecified) mysql error. What am I doing wrong, 
> and how can I fix this?
> Or is it simply that I'm pushing too hard and it can't be done and 
> must resort
> to a server-side language to loop it?

Frank,

Just lose the VALUES() part, which is not used in combination with SELECT:

INSERT
     INTO table2
         (  `meta_key`
          , `meta_value`
          , `post_id`)
SELECT DISTINCT '_sfr_places_icon_id'
          , 652
          ,
         p.ID
         FROM table1 p
         INNER
             JOIN table2 m
             ON m.post_id = p.ID
         WHERE p.post_type = 'sfr_place'
         AND p.ID NOT IN
             (
             SELECT k.post_id
             FROM table2 k
             WHERE k.meta_key = '_sfr_places_icon_id'
              )
         ORDER BY p.ID ASC
;

I didn't look too closely at the rest of the SQL statement but you can 
run the SELECT part of the query by itself in order to make sure it 
matches what you want.

Cheers,

Mattias