Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>Hi Jay, I spoke a little too soon. Only one field was updated in Table2, the first one that meets the criteria but the rest are not. How can I get them all to be updated?
>Sorry about your headache, hope you get better soon.
>Thanks
>Max
>
>>Thanks Jay! The results are exactly as I wanted!
>>
>>>>Max --
>>
>>>>You need to add a SQL 89 join condition to the UPDATE to join the table and >>temporary query together, as follows:
>>
>>
>>>>SELECT emp_name, commission, rptdate ;
>>>> FROM Table1 INNER JOIN Table2 ;
>>>> ON Table1.emp_name = Table2.emp_name;
>>>> WHERE Table1.rptdate = Table2.rptdate;
>>>> ORDER BY Table1.emp_name;
>>>> into cursor crsTemp
>>
>>>>UPDATE Table2 SET Table2.commission = crsTemp.commission ;
>>>> WHERE Table1.emp_name = Table2.emp_name;
>>>> AND Table1.rptdate = Table2.rptdate
>>
>>
>>>>Jay
>
>Glad that worked! I've got a headache today, and have a bit of tunnel vision. As Robert mentioned, the UPDATE as shown above should work by itself without the intermediate query.
>
>Jay
I'm a victim of false memories! Sergey was right -- no join between 2 tables is possible with UPDATE.
I'd suggest using good ol' xBase commands.
Set the relation between T1 and T2 based on an index of the PK values (name and date). Then do a REPLACE ALL t2.commission WITH t1.commission.
Best wishes, and sorry for the bad lead.
Jay
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement