How to update table with select from other table

[Tatar_1]

I am trying to update for some products their category in Database. But I get an error and and I dont understand where my statement is wrong. Please help me. I want to find products that have in their name a specific word and after that I want to update the category for this products.

I want to select IDs from sho_posts where sho_posts.post_title contain this part of word '%Audio CD%' and after that to update the sho_term_relationships.term_taxonomy_id with value 2 where sho_term_relationships.object_id=sho_posts.id

I am using this one:

UPDATE sho_term_relationships 
   SET term_taxonomy_id = 2
 WHERE object_id = (SELECT `id` FROM `1876522_shoping`.`sho_posts` WHERE CONVERT(`post_title` USING utf8) LIKE '%Audio CD%')

but get error "Subquery returns more than 1 row"

[abelingaw]

Try using a variable to store that ID with a SELECT statement and change your query with some AND.

[Tatar_1]

and how can I do this?

[pzuurveen]

use IN

UPDATE sho_term_relationships 
   SET term_taxonomy_id = 2
 WHERE object_id IN (SELECT `id` FROM `1876522_shoping`.`sho_posts` WHERE CONVERT(`post_title` USING utf8) LIKE '%Audio CD%')