I am converting the following DBISAM UPDATE statement to ELEVATEDB. I am updating the
SequenceNo in TestResults table with associated SequenceNo in Test table, if the
SequenceNos are different.

UPDATE
  TestResult
SET
  SequenceNo = Test.SequenceNo
FROM
  TestResult INNER JOIN Test
     ON TestResult.TestID = Test.ID
WHERE
  TestResult.SequenceNo <> Test.SequenceNo;

The EVLEVATEDB UPDATE statement below updates EVERY row in the table if ANY TEST has a
different SequenceNo in the TestResult & Test tables.   I believe that the statement below
should only update the SequenceNo if the SequenceNo in TestResults is different from the
SequenceNo for the same Test in the Test table.  (I have tested it with FIREBIRD and it is
only updating the TestResults where the SequenceNos are different)

UPDATE
  TestResults
SET
  SequenceNo = (SELECT SequenceNo FROM Test
                             WHERE TestResults.TestID = Test.ID)
WHERE EXISTS
  (SELECT * FROM Test
     WHERE
        TestResults.TestID = Test.ID AND
        TestResults.SequenceNo <> Test.SequenceNo);

CREATE TABLE "Test"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 146, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(24) COLLATE "ANSI_CI" NOT NULL,
"SequenceNo" INTEGER NOT NULL,
"Abbreviation" VARCHAR(12) COLLATE "ANSI" NOT NULL,
. . . . .
)
  
CREATE TABLE "TestResult"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1353957, INCREMENT BY 1) NOT NULL,
"ClientEpisodeID" INTEGER NOT NULL,
"TestID" INTEGER NOT NULL,
"Result" FLOAT,
"SequenceNo" INTEGER DEFAULT 0  NOT NULL,
. . . . .
)

--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone:    61 2 4930 7336
Mobile:    0419 016 032
email:    rharding@wck.com.au

Richard,

<< The EVLEVATEDB UPDATE statement below updates EVERY row in the table if
ANY TEST has a different SequenceNo in the TestResult & Test tables.   I
believe that the statement below should only update the SequenceNo if the
SequenceNo in TestResults is different from the SequenceNo for the same Test
in the Test table.  (I have tested it with FIREBIRD and it is only updating
the TestResults where the SequenceNos are different) >>

Yep, it's a bug.  Basically the EXISTS() function was getting past our
checks for correlated sub-queries, resulting in improper evaluation.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim..

--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone:   61 2 4930 7336
Mobile:   0419 016 032
email:   rharding@wck.com.au