How to update many rows which match conditions in SQL

Question

I am trying to update rows in a database (a wordpress e-commerce installation) so that all rows in which the stock row with the with the same id is 0, are set to 'outofstock' currently I get the error 'SQL Error 1064'

UPDATE `squirrel`.`wp_postmeta`
SET LEFT(`meta_value`, 256) = 'outofstock'
WHERE `post_id` IN (SELECT `post_id` FROM `squirrel`.`wp_postmeta` WHERE 
`meta_key` = '_stock' 
AND LEFT(`meta_value`, 256) = 0) AND `meta_key` = '_stock_status'; 

What is wrong with my code?

Answer 1

The above comments were helpful but there were other problems that turned up later. In the end here was the solution

UPDATE `squirrel`.`wp_postmeta`
SET `meta_value` = 'outofstock'
WHERE `post_id` IN (SELECT * FROM (SELECT `post_id` FROM 
`squirrel`.`wp_postmeta` WHERE `meta_key` = '_stock'
AND `meta_value` = 0) AS custom)  AND `meta_key` = '_stock_status';

The main thing being that by creating two nested SELECT statements according to here which meant that update didn't complain from working from a table which it was updating. Then I followed this to make sure this new table had it's own alias. This did what I wanted it to do.

Answer 2

You should use REPLACE in stead of LEFT as it will give you 1064 error

 UPDATE YourTable
 SET yourcolumn= REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')
 WHERE yourcolumn LIKE '%PATTERN%'