some error in WHERE clause

2 comments

1. AND (wp_posts.post_type='attachment') 
   

   I think the real issue here, is that the only place you’re referencing wp_posts is in your EXISTS and SET subqueries. That part of the WHERE clause (quoted above) is outside the scope of your subqueries and doesn’t know about it.

   Unless you meant for your EXISTS subquery to look like this:

   (select 1 
    from wp_posts 
    join articles on wp_posts.post_name=articles.image
    WHERE wp_posts.post_type='attachment')
   

   In this case, wp_posts is referenced in the same set of parens and is syntactically correct. Since this probably won’t give you what you’re after, try this instead:

   UPDATE wp_postmeta
   INNER JOIN wp_posts ON wp_posts.id = wp_postmeta.post_id
   SET meta_value = (SELECT DISTINCT id FROM wp_posts WHERE [some condition]) 
   WHERE EXISTS (select 1 
             from wp_posts 
             join articles on wp_posts.post_name=articles.image)
       AND (wp_posts.post_type='attachment') 
       AND (wp_postmeta.meta_key='_thumbnail_id' )
   

   Note #1: I’m making an assumption that your wp_postmeta table has a post_id field that corresponds to id on wp_posts.

   Note #2: As OMG Ponies stated…

   SET meta_value = (SELECT DISTINCT id FROM wp_posts) 
   

   This line will most-likely throw an error for returning multiple values where only 1 is expected. Note that I’ve added a “WHERE [some condition]” above. You’ll want to add your own WHERE clause here. And it should be something that limits the subquery result to only one result.

   Log in to Reply

2. You should move your right parenthesis in:

   (SELECT DISTINCT id FROM wp_posts)
   

   to the end of the statement.

   Log in to Reply