How do I substract a number from an entire integer column in SQL?-CodePudding

I am using SQL Server. I have a table (Sales) with a column (Number) that contains integers, for example 22000001, 22000002 etc.. Now I want to permanently edit the records by substracting 1.000.000 from that number for every row in the table, so that it will be 21000001, 21000002, etc..

I tried: SELECT Number -= 1000000 FROM Sales; This only gives me a syntax error.

CodePudding user response：

To modify the records permanently, you'll need to use an UPDATE statement instead of a SELECT statement (query).

The statement takes the form:

UPDATE [Table] SET [Column 1] = [Value 1], [Column 2] = [Value 2] WHERE [Some Condition]

Your [Table] is "Sales", your [Column 1] is "Number", [Value 1] is the original value minus 1000000, and you're modifying every row in the table so you don't need [Some Condition].

Your resulting query should be:

UPDATE Sales SET Number = Number - 1000000