Is it possible to update a table by adding values from other two tables in SQLite

Question

I am trying out SQLite and encountered a problem. There are 3 Tables A, B, and C. I want to update Table A using the sum of B and C.

Table A. James null.

Table B. James 5.

Table C James 2

so with the update, I want table A to have

James 3. (5-2)

Thank You

Answer 1

SQLite does not support joins in an UPDATE statement so you can do it by accessing directly the corresponding rows of the tables A and B like this:

update A 
set value = 
  (select value from B where name = A.name) -
  (select value from C where name = A.name)

If you want to update only the row with name = 'James' then add:

where name = 'James'

See the demo

Answer 2

Works in every DB:

UPDATE 
  "A" 
SET
  "x" = 
    (
      SELECT
        SUM("x")
      FROM "B"
        WHERE "B"."id"="A"."id"
    ) +
    (
      SELECT
        SUM("x")
      FROM "C"
        WHERE "C"."id"="A"."id"
    )

Answer 3

I believe the following demonstrates that Yes you can:-

DROP TABLE IF EXISTS ta;
DROP TABLE IF EXISTS tb;
DROP TABLE IF EXISTS tc;
CREATE TABLE IF NOT EXISTS ta (name TEXT, numb INTEGER);
CREATE TABLE IF NOT EXISTS tb (name TEXT, numb INTEGER);
CREATE TABLE IF NOT EXISTS tc (name TEXT, numb INTEGER);
INSERT INTO ta VALUES ('JAMES',null),('Mary',100);
INSERT INTO tb VALUES ('JAMES',5),('Sue',33);
INSERT INTO tc VALUES ('JAMES',2),('Anne',45);

UPDATE ta SET numb = 
    (SELECT sum(numb) FROM tb WHERE name = 'JAMES') 
    - 
    (SELECT sum(numb) FROM tc WHERE name = 'JAMES') 
WHERE name = 'JAMES';

SELECT * FROM ta;
SELECT * FROM tb;
SELECT * FROM tc;

This :-

• Drops the tables if they exist allowing it to be rerun (simplifies modifications if need be).
  • column names name and numb have been assumed as they weren't given.
• Creates the 3 tables (note table names used for the demo are ta, tb and tc)
• Adds some data (note that additional rows have been added to show how to distinguish (at least to a fashion))
• Updates column numb of table A (ta) where the name column has a value of JAMES according to the sum of the numb column from all rows with the same name (JAMES) from table tb minus the sum of the numb column from all rows with the same name (JAMES) from table tc
  • This may not be exactly what you want so it assumes that you want to sum all rows with the same name per table (ta and tc)
• Queries all the tables (first is shown below as that is the table that has been updated.)

The first result showing that the row has been updated from null to 3 (5 - 2) and that the row for Mary has remained as it was :-

The following change to the UPDATE gets the name (rather than hard-coding 'JAMES' multiple times, as per the row(s) extract from the ta table, the use of hard-coded names perhaps making it easier to understand the working of the SQL).

UPDATE ta SET numb = (SELECT sum(numb) FROM tb WHERE name = ta.name) - (SELECT sum(numb) FROM tc WHERE name = ta.name) WHERE name = 'JAMES';

Note that should there not be an associated row (i.e. with the same name) in either tb or tc then the result will be null (whether or not sum is used).