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;