update table1
set  value = (select sum(value) from table2 where myid = table1.myid)

I think the short answer is no..

Thanks
Clive.

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.
>

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.
>>
>
>

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

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

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