>>>>>Hi all ...
>>>>>
>>>>>The section of code below is one which I changed recently to use Update instead of Replace commands to avoid an issue arising with certain clients.
>>>>>Now, however, I am experiencing a strange problem with the update statement under certain circumstances.
>>>>>
>>>>>UPDATE Loop SET;
>>>>> dRejDate = Iif(WORKORDR.lReject And Empty(dRejDate),WORKORDR.dWrDate,dRejDate),;
>>>>> lRejVer = .F.,;
>>>>> lRejent = WORKORDR.lReject,;
>>>>> lCalEnter = .T.,;
>>>>> lCalVerify = WORKORDR.lReject,;
>>>>> nWorkID = Iif(WORKORDR.lReject,0,nWorkID);
>>>>>WHERE Loop.nLoopID = workordr.nEquipID
>>>>>
>>>>>I am updating a single record in our "Loop" table identified by the unique reference Workordr.nEquipID. In most cases, this update proceeds correctly, and the record pointer remains on the Loop record in question. In some instances, however, the update command causes the record pointer to move to the last record in the table. I have verified that an update does occur as _TALLY=1 after the update. If we attempt to run the same update command again, after this type of adverse action, the same problem arises. For this reason, I feel that the issue must be with some global variable or SET parameter, but I haven't been able to track it down. Does anyone know of what can cause the update to act in such a way.
>>>>
>>>>Mark,
>>>>That's normal behavior. You shouldn't expect your pointer to be preserved at all times using Update-SQL, TAbleupdate() etc.
>>>>You might try replace instead since you're using one-to-one.
>>>>Cetin
>>>
>>>Are you sure that this is normal behaviour ?? ... the frustrating thing is that in some cases (ie where the code runs OK), the record pointer is NOT moved by the same section of code ... if it was happening all of the time, I could rewrite with this in mind.
>>
>>If it's not it should be, so yes I'm sure. I didn't say at all times however. What's wrong with simple replace? Update SQL doesn't support currentof clause as SQL server does.
>>Cetin
>
>Well, you see, the reason we decided to use the UPDATE command instead of REPLACE was due to the fact that certain REPLACE commands were not updating a table correctly, and causing a major bug on one of our sites. The This occurred only once every few months, and was not reproducible. I found this hard to accept, but upon consultation with our senior developer, we decided to change the REPLACE commands to UPDATE. This is why we would be slow to use REPLACE instead. Is the behaviour of the update command documented anywhere that you know of ?


Replace would fail for example if you're at eof() in current workarea. Update behavior is similar, if it's a single record and does the update it'd stop there. If ID in WorkOrdr do not have a match where should it be? Or doing a series of bulk updates and one of the records is locked, where should be?
Update-SQL is documented in online help.
Cetin