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?

You're trying to assign a value to a function: "SET LEFT(..." — Robert Kock, Apr 05 '18 at 12:27
SET LEFT('meta_value', 256) = 'outofstock' wrong here. You've to specify field name instead function. :( — Rajib Chy, Apr 05 '18 at 12:37

score 0 · Answer 1 · answered Apr 05 '18 at 13:19

0

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

answered Apr 05 '18 at 13:19

Pankaj Kumar

550
2
6
22

score 0 · Accepted Answer · answered Apr 09 '18 at 09:15

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.

How to update many rows which match conditions in SQL

2 Answers2