Steberz Steberz - 1 year ago 64
MySQL Question

How would you add a number to every piece of data in a column in SQL?

So if I have one column of data called credit_debt that has ten different numbers in it, and I wanted to add 100 to each of those, how would I do that? I know that I could do it manually one by one, but how would I do it all in one command?

Answer Source

To update all of the rows in a table, we can issue an UPDATE statement without a WHERE clause.

We can reference the current values stored in columns in the UPDATE statement.

Assuming that credit_debt column is a numeric datatype (e.g. INT, DECIMAL, DOUBLE, et al.)

 UPDATE mytable 
    SET credit_debt = credit_debt + 100 

Before running an UPDATE like that, I always ensure that I have a good backup, and a way to restore to the current state. And I test my expressions in a SELECT, so I won't have to do a restore. Before running that UPDATE, I'd run a SELECT like this:

 SELECT credit_debt 
      , credit_debt + 100  AS _new_credit_debt
   FROM mytable
  ORDER BY ... 

And the verify that the value returned for _new_credit_debt is the value I want to assign to the column. (We can add whatever other expressions to the SELECT list we want, so we can verify the results.