I wrote a simple sql select syntax which will select all desired rows (items):

SELECT p.post_title, t.name
FROM
wp_posts AS p,
wp_terms AS t,
wp_term_taxonomy AS x
WHERE
t.term_id = x.term_id AND
x.taxonomy = 'category' AND
t.name = 'CatName'

I want to change it to UPDATE syntax which will set wp_posts.status to ‘trash’ each ‘CatName’ (the code above).

How should I change it so?

I tried with this (it wont work):

UPDATE p
SET p.post_status = 'trash'
FROM
wp_posts AS p,
wp_terms AS t,
wp_term_taxonomy AS x
WHERE
t.term_id = x.term_id AND
x.taxonomy = 'category' AND
t.name = 'CatName'

UPDATE
wp_posts p,
wp_terms t,
wp_term_taxonomy x
SET p.post_status = 'trash'
WHERE
t.term_id = x.term_id AND
x.taxonomy = 'category' AND
t.name = 'CatName'

When I said that “it can’t work”, i meant to say that it will not result in the expected result.

SELECT p.post_title, t.name
FROM
wp_posts AS p,
wp_terms AS t,
wp_term_taxonomy AS x
WHERE
t.term_id = x.term_id AND
x.taxonomy = 'category' AND
t.name = 'CatName'

Is the same as:

SELECT p.post_title, t.name
FROM
wp_posts AS p

You probably want something like:

SELECT p.post_title, t.name
FROM
wp_posts AS p,
wp_terms AS t,
wp_term_taxonomy AS x
WHERE
t.term_id = x.term_id AND
x.taxonomy = 'category' AND
t.name = 'CatName' AND
p.term_id = t.term_id; // Link up the P table to something.

Why will the following code produce this error: #1064 - 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 'LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id) WHERE p.post_type ’ at line 3

UPDATE wp_posts AS p
SET p.post_status = 'trash'
LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id)
WHERE p.post_type = 'post'
AND r.term_taxonomy_id != 22

I get the same result if I try this instead:

UPDATE p
SET p.post_status = 'trash'
FROM wp_posts AS p
LEFT JOIN wp_term_relationships AS r ON (p.ID = r.object_id)
WHERE p.post_type = 'post'
AND r.term_taxonomy_id != 22