Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 6 of 6 total |
Is it possible to do something like this? |
Wed, Apr 5 2006 10:07 PM | Permanent Link |
"Clive" | update table1
set value = (select sum(value) from table2 where myid = table1.myid) I think the short answer is no.. Thanks Clive. |
Thu, Apr 6 2006 12:05 AM | Permanent Link |
"Donat Hebert \(WSI\)" | Short answer is correct ..no
You'll need to group into a memory table, then update once table with the other. Donat. "Clive" <dd@dddd.com> wrote in message news:536CE7B3-C0D3-494A-AE9D-7C73D8E851D5@news.elevatesoft.com... > update table1 > set value = (select sum(value) from table2 where myid = table1.myid) > > I think the short answer is no.. > > Thanks > Clive. > |
Thu, Apr 6 2006 12:41 AM | Permanent Link |
"Clive" | Thought so...
What im trying to do is this. TableA Date1 Date Date2 Date Value Float Value2 Float Data looks like this DATE1 DATE2 VALUE 01Mar06 01MAR06 100 01Apr06 01MAR06 100 01May06 01MAR06 100 01Jun06 01Apr06 100 01Jul06 01Apr06 100 Want to update column Value2 with SUM(value) where DATE2 = DATE1 so for example the row 01Mar06 should have VALUE2 = 300 being the sum of all rows with DATE2 = 01Mar06 01Apr06 should have VALUE2 = 200 being the sum of all rows with DATE2 = 01Apr06 and so on Currently I have only been able to do this with lots pascal code and lots of updates in a loop which is slow. Anyone have a great idea on how to implement with temp tables etc in a few SQL steps? Cheers Clive. "Donat Hebert (WSI)" <dhebert@worldsoftwareinc.com> wrote in message news:F43ADDC2-E49A-4756-812C-8A45C9AA298A@news.elevatesoft.com... > Short answer is correct ..no > You'll need to group into a memory table, then update once > table with the other. Donat. > > > "Clive" <dd@dddd.com> wrote in message > news:536CE7B3-C0D3-494A-AE9D-7C73D8E851D5@news.elevatesoft.com... >> update table1 >> set value = (select sum(value) from table2 where myid = table1.myid) >> >> I think the short answer is no.. >> >> Thanks >> Clive. >> > > |
Thu, Apr 6 2006 2:23 AM | Permanent Link |
"Julio C. Saucedo Montenegro" | Mmm, I think that solution is something like this:
select myid, sum(value2) as sumvalue into "\memory\memtable2" /*version 4 sintax*/ from table2 group by myid; update table1 t1 set value = t2.sumvalue from table1 t1 inner join "\memory\memtable2" t2 on t1.myid = t2.myid in your case table2 of the first query will be the same table1 and the group field will be the date2. Regards. Julio Saucedo |
Thu, Apr 6 2006 4:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Clive,
<< update table1 set value = (select sum(value) from table2 where myid = table1.myid) I think the short answer is no.. >> You are correct. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 6 2006 5:14 PM | Permanent Link |
"Clive" | Thanks Julio. You solution was very close just needed to group up by DATE2
and myid then do the update joining back to DATE1.and myid But you put me on the right track and things are loads faster now. Many Thanks Clive. "Julio C. Saucedo Montenegro" <"juliosaucedo[nospam]"@gmail.com> wrote in message news:A68508FF-4C3F-4036-8F89-495321A645E0@news.elevatesoft.com... > Mmm, I think that solution is something like this: > > select myid, sum(value2) as sumvalue > into "\memory\memtable2" /*version 4 sintax*/ > from table2 > group by myid; > > update table1 t1 > set value = t2.sumvalue > from table1 t1 > inner join "\memory\memtable2" t2 on t1.myid = t2.myid > > in your case table2 of the first query will be the same table1 and the > group field will be the date2. > > Regards. > > Julio Saucedo |
This web page was last updated on Friday, April 19, 2024 at 07:09 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |