如何根据其他列中的值（对于同一行）更新表的所有行中的列？

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:

如果SQL不能以这种方式工作,我提前道歉,我主要使用C ++ / C#进行编码,而且我没有太多的SQL经验。我基本上想要遍历表的每一行,并根据其他列更改列值。示例表可能如下所示:

__________________________
|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?

什么是获得SQL等效的方法,或者不能以这种方式完成?

2 个解决方案

#1

This could be simple as this,

这可能很简单,

UPDATE tableName
SET    thirdCol = firstCol + secondCol

#2

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.

然而,存储caclulated值,特别是对于简单的计算是不好的做法(总是有例外,这是一个指导而非规则),如果column3应该总是Column1和column2的总和,那么如果你的DBMS允许它你可以创建一个计算柱。

e.g. In SQL-Server:

例如在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):

如果您的DBMS不允许计算列,那么您可以创建一个视图,因此您只存储基础数据而不是计算(再次语法可能因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

即使更新了column1或column2,这些方法中的任何一种都将确保您的column3值保持最新

#1