[thelist] SQL: Insert with FK subselect?

Mattias Thorslund mattias at thorslund.us
Wed Jun 15 14:47:03 CDT 2011

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?


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

     INTO table2
         (  `meta_key`
          , `meta_value`
          , `post_id`)
SELECT DISTINCT '_sfr_places_icon_id'
          , 652
         FROM table1 p
             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.



More information about the thelist mailing list