Update instead of insert (3 tables)

Databases
1

Hello,

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'

What am I doing wrong?

Thanks for help!

2

please check your syntax in da manual – nowhere will you find SET followed by FROM

3

So I should just move the SET/FROM around and it should work?

4

Heres the updated code (still wont work):

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'
5

what does “still won’t work” mean?

did it run or give an error?

6

He has no link to the P(wp_posts) table, so it can’t work.

7

actually, i think it can (although i’m not going to bother to test it)

it will likely update every row of p, if i’m not mistaken

8

How should I do this?

Exactly.

9

in post #1, you said your SELECT “will select all desired rows”

are you sure? because the failure to join properly should have returned all rows there, too

10

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.

Add more records in your database when testing.

11

Thanks for your help!

One more thing:

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

What am I doing wrong?

Thanks alot!

12

you are not checking in da manual for the correct syntax, that’s what

:slight_smile: