Update cursor from another cursor for all columns (without using the SET <fld>=<val> for 2

[Rajesh Karunakaran]

Hi friends,

I have a table with some records. I have a cursor with the new values for the records for the table. I want to update the table with new values for the matching values of the key column. If I use the UPDATE command, I have to write SET <fld> = <value> for each and every column. Is there a syntax (I couldn't find it yet though) of UPDATE command for this, ie eliminating the SET portion as I want all columns to be updated? As far as I know, the INSERT command will work like this without mentioning which columns to be updated while inserting the record, but not the UPDATE command. Any idea/thoughts?

Thanks in advance
Rajesh

 

[Olaf Doschke]

No, there is no such update syntax. the only way to update fields without naming fields is GATHER, nowadays also from an object.

But the question is, why you don't have the changes in a separate cursor, you use buffering to have changes in the original dbf or view cursor and then are able to TABLEUPDATE() for saving, TABLEREVERT() for canceling and SETFLDSTATE for handling individual fields.

Using a secondary cursor is a bad idea. If you got there through using the safe select technique for grids, well that's a good strategy for reading data, but not for modifying. Grid blanking problems can also be avoided through views and cursoradapter cursors in conjunction with REQUERY() and buffering and that's to be used for editing acces of DBFs or remote data with anything, not only regarding grids.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

As Olaf said, there is no way of using UPDATE without naming the fields to be updated.

However, you can achieve your goal with non-SQL syntax. Something like this (not tested):

SELECT NewValues
SCAN 
  SCATTER MEMVAR 
  SELECT ExistValues
  LOCATE FOR ExistValues.ID = NewValues.ID
  IF FOUND()
    GATHER FROM MEMVAR
  ENDIF     
ENDSCAN

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Yes Mike, that's what I did then. Thanks!
Thank you for the insight, Olaf.

Rajesh

 

[Olaf Doschke]

If you have lots of records to update that way, besides reasking why split at all, it can pay to generate the necessary UPDATE code with macro substitution as that causes one compile of an UPDATE SQL command for thousands or millions of records updates without a loop.

One situation I have done this was data transfer in a system taking data offline and then sending it back and merging changes into the central database. You generate the field list with the help of AFIELDS and then have that in a string variable for macro substitution: cursor1.field1 = cursor2.field1, cursor1.field2 = cursor2.field2,... I actually made the field list part of the data transfer mechanism via metadata, just like about UpdatableFieldList is a property of a view telling which fields it should update. Because you may not want to update many things. The main topic was to use netbooks in a lab connected to electronic scales, so mainly weights were stored into a local database, transferred back and then just a bunch of fields including the measured weight were updated.

So you can go that route, it's not impossible.

Someone once pointed out to me for such cases you can also use a thing called offline views. Yes, in part you can, but the offline work is a bit more complicated and doesn't only need a few records offline, so indeed the local netbook database had some parts of data including user data replicated fully, also to be able to reuse libraries that I surely didn't extend for offline view usage.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mukesh Khandelwal]

Hi Rajesh

You can also create a function which will take parameters as Source cursor, Target Cursor, Comma separated field list to Update( blank for all fields)
and you can create a SQL UPDATE statement for target table using the following procedure. I just written the below code and is not tested so please test it before using it

Procedure updaterecord
LParameter Targettable, Sourcealias, FieldList
Select (Sourcealias)
Update_cmd = 'update '+Targettable+' set '
For FieldNumber = 1 To Fcount()
fldName = Upper(Field(FieldNumber))
IF !EMPTY(FieldList) AND !fld$FieldList
Loop
endif
If Lower(fldName)=='Key Field name you do not want to update'
Loop
Endif
If FieldNumber >1
Update_cmd = Update_cmd+','
Endif
Update_cmd = Update_cmd+fldName+'='+'?'+fldName
Endfor
Update_cmd = Update_cmd+' where KeyFieldName='+keyFieldValue
= SQLEXEC(SqlHandle,Update_cmd)
Return