>>>is something like this possible? I get an error, but I thought it should have worked:
>>>
>>>UPDATE EmpItems
>>> LEFT JOIN HistItems ON HistItems.eiKey = EmpItems.eiKey
>>> WHERE HistItems.DelFlag = 0
>>> SET EmpItems.itOnPosted = 1, EmpItems.itBalAmt = HistItems.itBalNew + HistItems.itPostCalc
>>>
>>>Maybe I'm doing something wrong?
>>
>>You are missing FROM clause. The syntax for UPDATE -SQL command is:
>>
>>UPDATE Target
>> SET Column_Name1 = eExpression1 [, Column_Name2 = eExpression2 ...]
>> [FROM [FORCE] Table_List_Item [[, ...] | [JOIN [ Table_List_Item]]]
>> WHERE FilterCondition1 [AND | OR FilterCondition2 ...]
>
>Thanks, UPDATE... FROM sounds strange, but I'll get used to it!
UPDATE FROM is actually a proprietary T-SQL (and VFP) syntax. Many other RDBMS don't support it.
There are 2 known blogs on the problems with UPDATE ... FROM such as one of my Brad Schulz blogs favorites:
Dear FROM clause
If it's not broken, fix it until it is.
My Blog