Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 16 total |
Update-statement and join does not work |
Sat, Mar 22 2008 2:16 PM | Permanent Link |
Uli Becker | 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 |
Sat, Mar 22 2008 6:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Mar 22 2008 7:20 PM | Permanent Link |
Uli Becker | 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 |
Sun, Mar 23 2008 3:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sun, Mar 23 2008 5:23 PM | Permanent Link |
"Uli Becker" | 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 |
Sun, Mar 23 2008 5:24 PM | Permanent Link |
"Uli Becker" | 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 |
Sun, Mar 23 2008 7:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Mar 24 2008 4:32 AM | Permanent Link |
Uli Becker | 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 |
Mon, Mar 24 2008 5:38 AM | Permanent Link |
Uli Becker | 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 |
Mon, Mar 24 2008 6:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |