Combining SELECT with UPDATE

[pealo86]

I have the following SELECT query for a Wordpress database:

SELECT * FROM wp_posts
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id = 442);

 

However I need to add an UPDATE statement in there too and have no idea how to go about it.

 

Basically I have a field called 'post_type'. Any record that is returned in the results of the SELECT query needs to have its 'post_type' field value changed to 'featured-property'.

 

Does anyone know how I would go about this?

[pealo86]

...so something along the lines of:

 

UPDATE wp_posts

SET post_type='featured-property'

WHERE some_column=post

[pealo86]

Okay bit of an update, I'm now using the following code:

 

UPDATE wp_posts
SET post_type='featured-property'
WHERE post_type IN (
    SELECT ID FROM wp_posts
    INNER JOIN wp_term_relationships
    ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy
    ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy = 'category'
    AND wp_term_taxonomy.term_id = 442
)

 

However it is throwing the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 11

 

I don't understand as there is no " character on line 11?

[Muddy_Funster]

does post_type = wp_posts.ID ?

[pealo86]

Hmmm I'm not sure? But I've just tried:

 

UPDATE wp_posts
SET post_type='featured-property'
WHERE wp_posts.ID IN (
    SELECT ID FROM wp_posts
    INNER JOIN wp_term_relationships
    ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy
    ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    AND wp_term_taxonomy.taxonomy = 'category'
    AND wp_term_taxonomy.term_id = 442
)

 

and got the same error about line 11

[fenway]

You're missing a close paren for the ON close.