Update table wp_postmeta column meta_value where meta_key column equals certain value-CodePudding

I would like to update (divide the value by 100) in table wp_postmeta column meta_value where meta_key = price_cents

eg.

My code:

UPDATE wp_postmeta SET meta_value = meta_value / 100 WHERE meta_key = price_cents;

Above SQL statemant gives me error: #1054 - Unknown column 'price_cents' in 'where clause'

How to do it?

CodePudding user response：

UPDATE wp_postmeta SET meta_value = meta_value / 100 WHERE meta_key = 'price_cents';

String literals belong in single-quotes.

The string 'price_cents' is stored as data, it's not a column identifier. I can understand why this is a bit confusing, because the wp_postmeta table is a form of Entity-Attribute-Value table, which tries to model virtual attributes by adding rows of data. But when writing SQL, you must treat the data values as data values, even though they seem to name virtual attributes.