Hi,

I try to use an update-statement like this:

UPDATE Rechnungen R SET R.Mahnung1 = CURRENT_DATE FROM Rechnungen R
RIGHT JOIN Mahnungen M ON
R.RechnungenID = R.MahnungenID
where M.Mahnstufe = 1;

Why does it not work in EDB?

Thanks. Uli

Uli,

<< Why does it not work in EDB? >>

Joins are not support in UPDATE and DELETE statements in EDB, at least not
for now.  You have to use a correlated sub-query instead.  However, I'm
having a hard time following your UPDATE statement.  Why the use of the ROJ,
when the result is that the UPDATE always occurs anyways ?  Also, your JOIN
is joining columns from the same table.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> However, I'm
> having a hard time following your UPDATE statement.  Why the use of the ROJ,
> when the result is that the UPDATE always occurs anyways ?  Also, your JOIN
> is joining columns from the same table.

Sorry about this confusion and wasting your time. I mistyped the statement (copy and paste
sometimes makes sense

UPDATE Rechnungen R SET R.Mahnung1 = CURRENT_DATE FROM Rechnungen R
RIGHT JOIN Mahnungen M ON
R.RechnungenID = M.RechnungenID
where M.Mahnstufe = 1;

That should be correct. I guessed that joins are not allowed with update-statements. I
tried a subquery, but it's quite slow though I am sure to have set all indexes properly.

Happy Eastern to you and your family!

Uli

Uli,

<< Sorry about this confusion and wasting your time. I mistyped the
statement (copy and paste sometimes makes sense >>

No problem.  This should work as a replacement:

UPDATE Rechnungen R SET R.Mahnung1 = CURRENT_DATE
WHERE R.RechnungenID = (SELECT M.RechnungenID FROM Mahnungen M
                                             WHERE M.Mahnstufe = 1)

Although it could be off a bit.   I'm still a bit not sure of why you were
using a ROJ before, and frankly, I'm shocked that DBISAM actually executed
it.  It's hard to imagine what DBISAM should do in the case where the ROJ
causes a set of NULL row values in the table that you're actually updating.
IOW, the Mahnungen table is driving the Rechnungen table, and not the other
way around.  And that type of scenario shouldn't be allowed in DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> This should work as a replacement:

> UPDATE Rechnungen R SET R.Mahnung1 = CURRENT_DATE
> WHERE R.RechnungenID = (SELECT M.RechnungenID FROM Mahnungen M
>                                              WHERE M.Mahnstufe = 1)

OK, that's a simple solution, which I don't like because it is quite slow
(there are some other conditions and entries which I didn't post here in
order to make the code more readable). Perhaps I have to investigate why it
is so slow, but that's a different thing.

> Although it could be off a bit.   I'm still a bit not sure of why you were
> using a ROJ before, and frankly, I'm shocked that DBISAM actually executed
> it.  It's hard to imagine what DBISAM should do in the case where the ROJ
> causes a set of NULL row values in the table that you're actually
> updating. IOW, the Mahnungen table is driving the Rechnungen table, and
> not the other way around.  And that type of scenario shouldn't be allowed
> in DBISAM.

It's not like this. I never tried exactly this statement in DBISAM. But I
know that update-join constructs are allowed there.

Actually the table Mahnungen (reminder) cannot have null values. This table
is created by a query against Rechnungen (invoices). After printing the
reminders the corresponding rows of invoices have to be updated with the
date of the reminder and some other entries.
Thus the fact "the Mahnungen table is driving the Rechnungen table" is
correct from my view.

I am just looking for a fast way to update the Invoices table depending on
the values in the Reminder table. But anyway I didn't want to take your time
investigating this. For me it was not clear that the update-statement
doesn't allow joins.

In addition I found a post from you here:
http://www.elevatesoft.com/newsgrp?action=searchopenmsg&group=17&msg=608&keywords=update*%20join*#msg608

where you propose:

<<
UPDATE "\Memory\SpecStates" SET MinAcquiredDT=AcquiredDT
FROM "\Memory\SpecStates" INNER JOIN Specs ON
Specs.SpecID=SpecStates.SpecID AND Specs.State=SpecStates.MinState;
>>

That's why I tried to find a solution using update AND join.

Thanks.

Regards Uli

Tim,

> This should work as a replacement:

> UPDATE Rechnungen R SET R.Mahnung1 = CURRENT_DATE
> WHERE R.RechnungenID = (SELECT M.RechnungenID FROM Mahnungen M
>                                              WHERE M.Mahnstufe = 1)

OK, that's a simple solution, which I don't like because it is quite slow
(there are some other conditions and entries which I didn't post here in
order to make the code more readable). Perhaps I have to investigate why it
is so slow, but that's a different thing.

> Although it could be off a bit.   I'm still a bit not sure of why you were
> using a ROJ before, and frankly, I'm shocked that DBISAM actually executed
> it.  It's hard to imagine what DBISAM should do in the case where the ROJ
> causes a set of NULL row values in the table that you're actually
> updating. IOW, the Mahnungen table is driving the Rechnungen table, and
> not the other way around.  And that type of scenario shouldn't be allowed
> in DBISAM.

It's not like this. I never tried exactly this statement in DBISAM. But I
know that update-join constructs are allowed there.

Actually the table Mahnungen (reminder) cannot have null values. This table
is created by a query against Rechnungen (invoices). After printing the
reminders the corresponding rows of invoices have to be updated with the
date of the reminder and some other entries.
Thus the fact "the Mahnungen table is driving the Rechnungen table" is
correct from my view.

I am just looking for a fast way to update the Invoices table depending on
the values in the Reminder table. But anyway I didn't want to take your time
investigating this. For me it was not clear that the update-statement
doesn't allow joins.

In addition I found a post from you here:
http://www.elevatesoft.com/newsgrp?action=searchopenmsg&group=17&msg=608&keywords=update*%20join*#msg608

where you propose:

<<
UPDATE "\Memory\SpecStates" SET MinAcquiredDT=AcquiredDT
FROM "\Memory\SpecStates" INNER JOIN Specs ON
Specs.SpecID=SpecStates.SpecID AND Specs.State=SpecStates.MinState;
>>

That's why I tried to find a solution using update AND join.

Thanks.

Regards Uli

Uli,

<< OK, that's a simple solution, which I don't like because it is quite slow
(there are some other conditions and entries which I didn't post here in
order to make the code more readable). Perhaps I have to investigate why it
is so slow, but that's a different thing. >>

Correlated sub-queries work just like joins, except that the joined table is
a query and not just a table and you can only do the equivalent of INNER
JOINs.

<< In addition I found a post from you here: >>

That was a DBISAM customer posting in the ElevateDB newsgroup by accident.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

sorry for the double post (new newsreader).

I wrote a script doing what I want now, though I need four steps for it. Is that what you
propose?: (Mahngebuehr is the additional fee for reminders)

SCRIPT
BEGIN
 DECLARE Mahngebuehr2 Float Default 0;
 DECLARE Mahngebuehr3 Float Default 0;
 DECLARE Result CURSOR FOR Stmt;
 Prepare Stmt FROM
   'select Mahngebuehr2, Mahngebuehr3 from optionen';
 Open Result;
 Fetch First FROM Result(Mahngebuehr2, Mahngebuehr3) INTO Mahngebuehr2, Mahngebuehr3;
 Close Result;
 Execute Immediate 'Update Rechnungen set Mahnung1 = Current_Date
                              where RechnungenID in (select RechnungenID from Mahnungen
where Mahnstufe = 1)';
 Execute Immediate 'Update Rechnungen set Mahnung2 = Current_Date,
                              Mahngebuehr2 = ' + Cast(Mahngebuehr2 as VarChar) + '
                              where RechnungenID in (select RechnungenID from Mahnungen
where Mahnstufe = 2)';
 Execute Immediate 'Update Rechnungen set Mahnung3 = Current_Date,
                              Mahngebuehr3 = ' + Cast(Mahngebuehr3 as VarChar) + '
                              where RechnungenID in (select RechnungenID from Mahnungen
where Mahnstufe = 3)';
 Execute Immediate 'Update Rechnungen set Inkasso = Current_Date
                              where RechnungenID in (select RechnungenID from Mahnungen
where Mahnstufe = 4)';
END

Tim,

additional information:

The script in my last post runs quite fast in about 0.5 seconds and updates about 100 rows.

Here the final procedure which has an additional condition (RechnungenID = ID of invoice)
in order to update just one single row if necessary.
Please note: I don't want you to make my work, it's just a question of comprehension in
order to code future procedures in a better way.

The execution time of this procedure is about 4 sec! though only one row is updated
(Indexes on Mahnstufe and RechnungenID in Mahnungen are set properly). When I replace the
paramaters by a hardcoded ID, it runs twice as fast, which I don't understand at all.

BEGIN
 DECLARE Mahngebuehr2 Float Default 0;
 DECLARE Mahngebuehr3 Float Default 0;
 DECLARE Condition VarChar;
 DECLARE Result CURSOR FOR Stmt;

 Prepare Stmt FROM
   'select Mahngebuehr2, Mahngebuehr3 from optionen';
 Open Result;
 Fetch First FROM Result(Mahngebuehr2, Mahngebuehr3) INTO Mahngebuehr2, Mahngebuehr3;
 Close Result;
 Prepare Stmt from
 'Update Rechnungen set Mahnung1 = Current_Date
 where RechnungenID in (select RechnungenID from Mahnungen
 where RechnungenID = ? and Mahnstufe = 1)';
 Execute Stmt using RechnungenID;
 Prepare Stmt from
 'Update Rechnungen set Mahnung2 = Current_Date,
 Mahngebuehr2 = ' + Cast(Mahngebuehr2 as VarChar) + '
 where RechnungenID in (select RechnungenID from Mahnungen where RechnungenID = ? and
Mahnstufe = 2)';
 Execute Stmt using RechnungenID;
 Prepare Stmt from
 'Update Rechnungen set Mahnung3 = Current_Date,
 Mahngebuehr3 = ' + Cast(Mahngebuehr3 as VarChar) + '
 where RechnungenID in (select RechnungenID from Mahnungen where RechnungenID = ? and
Mahnstufe = 3)';
 Execute Stmt using RechnungenID;
 Prepare Stmt from
 'Update Rechnungen set Inkasso = Current_Date
 where RechnungenID in (select RechnungenID from Mahnungen where RechnungenID = ? and
Mahnstufe = 4)';
 Execute Stmt using RechnungenID;

 /* Mahnungen für Protokoll bearbeiten */
 Execute Immediate 'Update Mahnungen set Mahnung1 = Current_Date where
 Mahnstufe = 1';
 Execute Immediate 'Update Mahnungen set Mahnung2 = Current_Date where
 Mahnstufe = 2';
 Execute Immediate 'Update Mahnungen set Mahnung3 = Current_Date where
 Mahnstufe = 3';
 Execute Immediate 'Update Mahnungen set Inkasso = Current_Date where
 Mahnstufe = 4';
END

Regards Uli

Uli

One thing I would definitely suggest is that you name your working variables/parameters differently to your fields. Tim's parser may be able to figure them out but it sure confuses me. It would also make the code more readable, at least in my opinion.

Roy Lambert