CODEWITHSUNDEEP
databasesqlite
deppen8
deppen8

Reputation: 153

SQLite multiply columns to update a third column

I am trying to do what I think should be a simple SQLite operation. I want to multiply two columns from separate tables and put the results in a new column in the first table. The following runs, but produces a column of all 0.0:

UPDATE table 1
SET columnC = (SELECT (table1.columnB * table2.columnX)
               FROM table 1
               INNER JOIN table2 ON table1.columnA = table2.columnA);

If I isolate the SELECT statement in parentheses and run it on its own, I get the right values, but I don't know how to insert these into a column in the table.

SELECT (table1.columnB * table2.columnX)
       FROM table 1
       INNER JOIN table2 ON table1.columnA = table2.columnA;

Any insight?

Upvotes: 1

Views: 779

Answers (1)

MikeT
MikeT

Reputation: 56938

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.

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) :- enter image description here

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) :-

enter image description here

Example 2 - restricting to 1 row using WHERE

Adding the WHERE's then the result is :-

enter image description here

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 :-

enter image description here

Upvotes: 2

Related Questions