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?
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.