>>I need some help with the syntax of an SQL statement where records should be either updated or replaced (I'm not sure which should be used).
>>If Table1 and Table2 has employees named Jane and John (emp_name field) and their report date (rptdate field) is the same in both tables then I would want the commission (commission field) that she gets in Table1 to be the same in Table2 (the Table2 commission field has a default of 0.00).
>>The following SQL statement updates every row in the colunm of Table2 with the same value but I want it to update each row in the column of Table2 with the corresponding value found in Table1 (provided the name and report date are the same in both). How can this be accomplished?
>
>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 ;
>  <b>WHERE Table1.emp_name = Table2.emp_name;
>     AND Table1.rptdate = Table2.rptdate</b>
>
>

>

Unfortunatelly, VFP SQL update and SQL Delete doesn't allow join conditions in the WHERE clause, only filter conditions.