CODEWITHSUNDEEP
sqlsql-update
proseidon
proseidon

Reputation: 2305

How do I update a column in all rows of a table based on values in other columns (for the same row)?

I apologize in advance if SQL doesn't work this way, I mostly code in C++/C# and I don't have much experience in SQL. I basically want to iterate through each row of the table and change a column value based on the other columns. An example table might look like this:

__________________________
|first #|second #|third #|
|_______|________|_______|
|___1___|___1____|___0___|
|___5___|___2____|___0___|
|___3___|___6____|___0___|
|___2___|___4____|___0___|

Now, in pseudo code, I am trying to get the equivalent of:

foreach row in mytable, column 3 = column 1 + column 2

What would be a way to get an SQL equivalent of this, or can it not be done in this way?

Upvotes: 10

Views: 6967

Answers (2)

GarethD
GarethD

Reputation: 69749

As has already been answered a simple update can be done using:

UPDATE  MyTable
SET     Column3 = Column1 + Column2;

However storing caclulated values, especially for simple calculations is not good practice (there are always exceptions and this is a guideline not a rule), if column3 should always be the sum of Column1 and column2 then if your DBMS permits it you could create a calculated column.

e.g. In SQL-Server:

ALTER TABLE MyTable ADD Column4 AS Column1 + Column2;

If your DBMS does not permit computed columns then you could create a view, so you are only storing the base data and not the calculation (again syntax may vary by DBMS): 

CREATE VIEW myTableWithTotal
AS
    SELECT  Column1, Column2, Column1 + Column2 AS Column2
    FROM    MyTable

Either of these methods will ensure your column3 value remains up to date even if column1 or column2 are updated

Upvotes: 6

John Woo
John Woo

Reputation: 263693

This could be simple as this,

UPDATE tableName
SET    thirdCol = firstCol + secondCol

Upvotes: 19

Related Questions