Creating an update query across two tables

[sorahp@yahoo.com]

So I've been trying to create an update query across two tables:

UPDATE "Projects" SET "Balance" = "ActualCost" - "Deposit" - (SELECT (SELECT SUM( "Payments" ), "ID" FROM "Payments" GROUP BY "ID") FROM "Payments" WHERE "Payments"."Project ID" = "Projects"."Project ID")

I have two tables, Project (which contains all projects and their costs and other information) and Payments (which keeps a record of all payments made against all projects).
For a specific project I want to record the current Balance by taking the Invoiced charge for the work, subtracting the initial deposit and subtracting all payments to that project.
Presently, there seems to be a null value when I include: - (SELECT (SELECT SUM( "Payments" ), "ID" FROM "Payments" GROUP BY "ID") FROM "Payments" WHERE "Payments"."Project ID" = "Projects"."Project ID")

[eremmel]

From payments you group by "ID", but filter on "Project ID"
You do not need a GROUP BY when you aggregate over all data in the selection.
What about:

Code: Select all

UPDATE "Projects" 
     SET "Balance" = "ActualCost" - "Deposit" - (SELECT SUM( "Payments" ) FROM "Payments" WHERE "Payments"."Project ID" = "Projects"."Project ID")
WHERE "Projects"."Project ID" = specific-project-that-you-like-to-specify

[UnklDonald418]

If you can get the code presented by eremmel to work then that is probably the way to go, but I will offer an alternate solution. In the past I too have struggled getting UPDATE to behave as intended, so I found your problem interesting. All of my attempts at editing your UPDATE command yielded the same results that you got (or worse). To me the problem appears to be related to the 1 to n relationship between “Projects” and “Payments”. So I decided to attack the problem differently.

Code: Select all

DROP TABLE IF EXISTS "TempSum";
CREATE TABLE "TempSum" ("ID" INTEGER  GENERATED BY  DEFAULT AS IDENTITY,"Project ID" INTEGER, "Sum" DECIMAL);
INSERT INTO "TempSum" ("Project ID","Sum" ) SELECT "Project ID", SUM( "Payments" ) 
FROM "Payments" GROUP BY "Project ID" ;
UPDATE "Projects"
SET "Balance" = "ActualCost" - "Deposit" - SELECT "Sum" FROM  "TempSum"  
WHERE "TempSum"."Project ID" = "Projects"."ID";
DROP TABLE IF EXISTS "TempSum";

This stores the sum of the payments in a temporary file that results in a one to one relationship between "Projects" and “TempSum” tables.

[UnklDonald418]

After looking at this a little more I noticed some issues with the code in my last post.
I forgot to declare that the table "TempSum" was actually TEMPORARY. Without that declaration each time the update is called the database file grows a little larger.
The table "TempSum" doesn't need "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY, the "Project ID" can serve as a primary key.
For Projects that have no payments recorded the Balance would be set to NULL. A second update takes care of that issue.

Code: Select all

DROP TABLE IF EXISTS "TempSum";
CREATE TEMPORARY TABLE "TempSum" ("Project ID" INTEGER, "Sum" DECIMAL);
INSERT INTO "TempSum" ("Project ID","Sum" ) SELECT "Project ID", SUM( "Payments" ) 
    FROM "Payments" GROUP BY "Project ID" ;
UPDATE "Projects"
   SET "Balance" = "ActualCost" - "Deposit" - SELECT "Sum" FROM  "TempSum"  
     WHERE "TempSum"."Project ID" = "Projects"."ID";
UPDATE "Projects"
   SET "Balance" = "ActualCost" - "Deposit" 
     WHERE "Balance" IS NULL;
DROP TABLE IF EXISTS "TempSum";