1

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?

Dropout
  • 13,653
  • 10
  • 56
  • 109
Ahmar Ali
  • 1,038
  • 7
  • 27
  • 52

1 Answers1

6

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.

BlitZ
  • 12,038
  • 3
  • 49
  • 68
  • 1
    +1 Also I would change `meta_value+meta_value*(0.60)` to `meta_value*(1.60)` – valex Feb 14 '14 at 08:43
  • 1
    @valex OP wants to increase value > `I am trying to update this value to a higher number (percentage increase in price).` – BlitZ Feb 14 '14 at 08:44
  • 1
    Sure but `X+X*0.60 = X*1.60` – valex Feb 14 '14 at 09:00
  • @valex yeah... Missed leading `1` digit - just thouht, that you suggesting to lower the value. Now, I see, that you are right. Updated. – BlitZ Feb 14 '14 at 09:01