CODEWITHSUNDEEP
mysql
Santhosh
Santhosh

Reputation: 11774

MYSQL, update only certain rows of a table which satisfy certain conditons

This is wordpress database.It has two tables wp_terms and wp_term_taxonomy

wp_terms has columns slug, name, term_id
wp_term_taxonomy has columns term_id, taxonomy, term_taxonomy_id.

Now I try

UPDATE wp_terms
SET slug = CONCAT(slug,'-quotes')
FROM wp_terms, wp_term_taxonomy
WHERE wp_term_taxonomy.taxonomy = "post_tag" AND wp_term_taxonomy.term_id = wp_terms.term_id

I am trying to concat the word in the rows of the column "slug" of table "wp_terms" which satisfy certain conditions.

but mysql gives

#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 'FROM wp_terms, wp_term_taxonomy WHERE wp_term_taxonomy.taxonomy = "post_tag" AN' at line 3

Upvotes: 2

Views: 112

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

Try this:

UPDATE wp_terms
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id
SET slug = CONCAT(slug,'-quotes')
WHERE wp_term_taxonomy.taxonomy = "post_tag"

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227200

FROM doesn't belong in an UPDATE statement. If you want to join tables in an UPDATE, you can use JOIN.

UPDATE wp_terms
JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id
SET slug = CONCAT(slug,'-quotes')
WHERE wp_term_taxonomy.taxonomy = "post_tag"

You can also do this:

UPDATE wp_terms, wp_term_taxonomy
SET slug = CONCAT(slug,'-quotes')
WHERE wp_term_taxonomy.taxonomy = "post_tag" AND wp_term_taxonomy.term_id = wp_terms.term_id

Upvotes: 1

Related Questions