更新查询中的Mysql类型转换

Question

I have a table with two columns meta_key and meta_value. Both of them are varchar now for meta_key _price the meta_value is a number like 10.0, 20.0 etc. I am trying to update this value to a higher number (percentage increase in price). I am trying to cast meta_value as integer but it is not working.

Here is what I am trying:

update wp_postmeta set CAST(meta_value AS UNSIGNED)=meta_value+meta_value*(0.60) 
where meta_key='_price'

But it gives this error:

error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST(meta_value AS UNSIGNED)=CAST'

What I am doing wrong?

Answer 1

Left-side assignment part of UPDATE statement specifies column to update, not the value. You should use CAST() on right side of assignment operation.

UPDATE
    wp_postmeta
SET 
    meta_value = CAST(meta_value AS UNSIGNED) * 1.60 
WHERE
    meta_key = '_price';

Quote from manual:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

The SET clause indicates which columns to modify and the values they should be given.