Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
UPDATE SQL, How to use with correlated query.
Message
De
10/05/2005 15:57:11
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
Visual FoxPro:
VFP 9
Database:
Visual FoxPro
Divers
Thread ID:
01011797
Message ID:
01012579
Vues:
19
>That's a great recomendation, this thread comes as a way to know how to improve the >FROM clause and correlated sentences with the new VFP9 UPDATE-SQL feature.

Well, with VFP9 you can also do subqueries and therefore it can also be done this way in one SQL-Update:

Update cAgent ;
SET yMonth = t2.YTot ;
FROM (Select iAgentID, Sum(yAmount) As YTot ;
FROM cMovs GROUP BY 1 ;
WHERE Year(dDate) = 2005 And Month(dDate) = 1) as t2;
WHERE t2.iAgentID = cAgent.iAgentID ;
AND cAgent.nYear = 2005 AND cAgent.nMonth = 1

But that doesn't make it faster than the two selects done one after another.

I'd say the speed would be improved if records in cMovs were physically ordered by time, because although a index speeds things up, if the hard disk drive head doesn#t have to move much, it may even get faster. And normally the records would be in time order, wouldn't they?

Bye, Olaf.
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform