Search code examples
sqljoomlaphpmyadminvirtuemart

Minus 1 from column value?

How would I go about subtracting "1" for all the values of a column in the database?

I have table: products

Columns: custom_id --- customfield_value

I want to reduce the value of "customfield_value" by 1 with a query.

I tried this but it was not working: 

UPDATE `products` 
SET `customfield_value` = (
   SELECT 
    customfield_value
   WHERE
    custom_id = 22
    customfield_value - 1
)
Solution

  • Why the nested select?

    UPDATE products
    SET customfield_value = customfield_value - 1;

    This affects all columns in the table -- as your question asks. Of course, you can add a WHERE clause for a particular row or set of rows:

    UPDATE products
    SET customfield_value = customfield_value - 1
    WHERE custom_id = 22;