[VSDK Macho] Wishes and performance stuff

[Frank Schima]

Hi Ruslan and Totte,


--- Ruslan Zasukhin <sunshine at public.kherson.ua> wrote:
> on 1/21/03 1:57 PM, Totte Alm at totte at code66.se wrote:
> 
> > UPDATE HolderTable  H, ShareRecord S SET H.Totalshares=SUM(S.shares)
> > WHERE S.parentHolder=H.recid
> > 
> > or this
> > 
> > UPDATE HolderTable  H, ShareRecord S SET H.TotalVotes=SUM(S.shares)
> > WHERE (S.parentHolder=H.recid) AND H.BuyBack=0
> 
> Btw, anybody can do this ?

Yes, Sybase, Oracle, PostgreSQL, and maybe MySQL can (to name a few) but Totte's syntax
is non-standard SQL. You can never have more than one table specified in the UPDATE
clause because only one table can be UPDATEd at a time. However, you can add a FROM
clause if you want to get values from other tables to UPDATE the one table. But I think
this example can be done with a Sub-SELECT (when we get them). 

Let's take the 2nd case above because it is more complex. Note that I use a FROM clause
to specify an alias for the HolderTable because that is the only place that is normally
allowed to be specified. 

UPDATE HolderTable
SET    H.Totalshares=
        (SELECT SUM(S.shares) FROM ShareRecord S WHERE S.parentHolder=H.recid)
FROM   HolderTable H
WHERE  H.BuyBack=0


Best Regards,
Frank


=====
Frank Schima                <mailto:frank at labrecyclers.com>
Lab Recyclers Inc.          <http://www.labrecyclers.com/>
Gaithersburg, MD USA        <http://www.ci.gaithersburg.md.us/>

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com