View Incident Report

Minor

Reported By: Ralf Mimoun
Reported On: 2/9/2006
For: Version 4.22 Build 4

# 2161 Using Joined UPDATE Statement Does Not Work for Calculating a Running Total

have a problem to generate sums. What I need is a result table looking like this:

Article ID / Sale sum / Order sum

Sales and orders are in different tables, so I can't use a simple SUM() GROUP BY Article.ID - the LEFT OUTER JOIN would lead to multiple records per sale/order. Example: I have one sale (say, 2 items) and two orders (say, 2 and 3 items). If I use a LEFT ORDER, I end up with two records: one with Sale = 1 and Order = 2, one with Sale = 1 and Order = 3. Other JOINs can lead to even more duplicate lines. Summing up Sale will give the wrong result (here: 2).

So, I want to create a temporary table, with two rows SaleSum and OrderSum, both with a value of 0. Then, I can run two different UPDATEs to set SaleSum and OrderSum: UPDATE TmpTable SET SaleSum = SaleSum + ...

Now my problem: that UPDATE simple does not work as expected. Here is the relevant SQL:

CREATE TABLE IF NOT EXISTS "a"
(
  "id" AUTOINC,
  "name" VARCHAR(10),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
LAST AUTOINC 3
);

INSERT INTO "a" VALUES (1, 'AA');
INSERT INTO "a" VALUES (2, 'AB');
INSERT INTO "a" VALUES (3, 'AC');

CREATE TABLE IF NOT EXISTS "B"
(
  "ID" AUTOINC,
  "A_ID" INTEGER,
  "V" INTEGER,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
LAST AUTOINC 6
);

INSERT INTO "B" VALUES (1, 1, 1);
INSERT INTO "B" VALUES (2, 1, 1);
INSERT INTO "B" VALUES (3, 1, 1);
INSERT INTO "B" VALUES (4, 2, 1);
INSERT INTO "B" VALUES (5, 2, 1);
INSERT INTO "B" VALUES (6, 3, 1);

SELECT
 A.*,
 0 AS SumB
INTO "MEMORY\Tmp"
FROM A;

UPDATE "MEMORY\Tmp" SET
 SumB = SumB + B.V
FROM "MEMORY\Tmp"
INNER JOIN B ON B.A_ID = "MEMORY\Tmp".ID;

SELECT * FROM "MEMORY\Tmp";

Resolution

Fixed Problem on 2/9/2006 in version 4.22 build 5

Products Affected

DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap