×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

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

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

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):

CODE -->

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

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

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

Rajesh

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

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close