SQLite将列相乘以更新第三列-SQLitemultiplycolumnstoupdateathirdcolumn

2  

I think your issue might be that you aren't restricting the update so it is applying it to the every row for every row so at the end of it columnC would be the same value throughout.

我认为您的问题可能是您没有限制更新，所以它将它应用到每一行的每一行，所以在它的末尾，columnC将是相同的值。

e.g. Assuming :-

例如假设:

DROP TABLE IF EXISTS table2;
CREATE TABLE IF NOT EXISTS table2 (columnA INTEGER,columnX INTEGER);
INSERT INTO table1 VALUES(1,5,0),(2,7,0),(3,8,0),(4,3,0);
INSERT INTO table2 VALUES(1,4),(2,3),(3,10),(4,21);

SELECT * FROM table1 JOIN table2 ON table1.columnA = table2.columnA;

The result (tables unchanged, so the base data, both tables joined highlighted is table 2 columns) :-

结果(表不变，所以基本数据，两个表连接的表都是表2列):-。

Example 1 - How you are doing it

Using (your way i.e. WHERE's commented out) :-

使用(你的方式，即哪里被注释掉):-

UPDATE TABLE1 SET columnC = 
    (SELECT (table1.columnB * table2.columnX)
           FROM table1
           INNER JOIN table2 ON table1.columnA = table2.columnA
           --WHERE table1.columnA = 3
    )
--WHERE columnA = 3
;

SELECT * FROM table1 JOIN table2 ON table1.columnA = table2.columnA

The result is (all ColumnC's changed to 20 i.e. 4 * 5 as per Table1.ColumnA * Table2.ColumnX for where ColumnA's are 1) :-

结果是(根据表1，所有列都变为20，即4 * 5。一列圆柱*表二。1) -

Example 2 - restricting to 1 row using WHERE

Adding the WHERE's then the result is :-

加上WHERE，结果是:-

Example 3 - Updating all rows

Now if you wanted to update all rows then you could use WITH ... UPDATE e.g.

如果你想要更新所有的行，你可以用…更新如。

WITH updates(ID, newvalue) AS (
    SELECT table1.columnA,
        table1.columnB * table2.columnX 
    FROM table1 
    INNER JOIN table2 ON table1.columnA = table2.columnA
)
UPDATE TABLE1 SET columnC = 
    (
    SELECT newvalue
    FROM updates
    WHERE table1.columnA = ID
    )
;

Which would result in :-

这将导致:- ?