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!

*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.

Jobs

Updating only 1 record in VFP 9.0

Updating only 1 record in VFP 9.0

(OP)
I have an interesting scenario:
I need to update the bottom or top record of a group of records that are exactly the same. (not my design, legacy software that is on its way out). I can't seem to find the correct logic in SQL to do this. I either update all of them or none of them. Is there a way to limit the number of rows or records that the SQL will update?

RE: Updating only 1 record in VFP 9.0

While it is very easy to do with VFP non-SQL commands, it appears as though you are needing to do this with a SQL Query/Update commands.

If you know the specific record number, then just include it in the criteria portion of your SQL statement.

Quote:

update the bottom or top record of a group of records
TOP being WHERE RECNO() = 1 and BOTTOM being WHERE RECNO() = RECCOUNT()

Quote:

records that are exactly the same
I'll throw in one other suggestion - that would be to eliminate the Duplicate records first

Good Luck
JRB-Bldr



RE: Updating only 1 record in VFP 9.0

With "real" SQL you never should care about record numbers or their natural order at all. Instead you make sure that each record has some unique identifier which has absolutely nothing to do with the data as such. Personally I always use GUIDs. If have started in the wrong way, so to speak, and if you are talking about DBFs, you can Update xxxx for Recno() = lnRecno. For this to work, you must determine lnRecno first.

RE: Updating only 1 record in VFP 9.0

When you talk about "top" and "bottom" records, do you mean first and last records in a given sequence? I guess you must mean that, otherwise the terms "top" and "bottom" don't have any meaning.

And if the records are in a given sequence, how can they all be the same?

Assuming that they are in a given sequence, and assuming they are sequenced on a field named ID, you could do something like this:

CODE -->

SELECT TOP 1 RECNO() AS RecTop FROM TheTable ORDER BY ID INTO ARRAY laTop
SELECT TOP 1 RECNO() AS RecBottom FROM TheTable ORDER BY ID DESC INTO ARRAY laBottom
SELECT TheTable
REPLACE FirstField WITH NewValue1, SecondField WITH NewValue2,  .... etc. ; 
  FOR RECNO() = laTop OR RECNO() = laBottom 

I haven't tested this, but it should give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Updating only 1 record in VFP 9.0

(OP)
SELECT TOP 1 RECNO() as RecTop FROM reinstrn WHERE date >= DATE(2017,08,01) AND Policy Like 'BOP1900000263' AND code NOT like 'MBR1' ORDER BY Policy INTO ARRAY laTop

SELECT reinstrn Replace reinsprem WITH 33 WHERE Recno = laTop

I am getting an error saying "Command contains unrecognized phrase/keyword"

RE: Updating only 1 record in VFP 9.0

Update reinstrn set reinsprem = 33 WHERE Recno() = laTop 

When you get an error, don't forget to check Help!

RE: Updating only 1 record in VFP 9.0

(OP)
shouldn't Recno = 1 work because this would be the top record always?

so UPDATE Reinstrn SET Reinsprem = 33 WHERE date >= DATE(2017,08,01) AND Policy Like 'CPP1400001367' AND RECNO()=1

RE: Updating only 1 record in VFP 9.0

Not if record 1 doesn't match your conditions. One advice: always study the code samples we give you. If you don't understand them, you learn nothing.

RE: Updating only 1 record in VFP 9.0

Quote:

shouldn't Recno = 1 work

Remember that RECNO() and RECNO are different.

RECNO() is a valid Visual Foxpro function returning a Record Number.
RECNO is NOT a valid VFP command or function.

They are not interchangeable.

But I still recommend that you eliminate the Exact Duplicate records before you do anything else.
Obviously if, as you indicate, there are records which are EXACT Duplicates the application should not 'know' the difference.
Maybe, after making a backup copy of the data table, you should try this and see how things work.

Alternatively, if the records are indeed EXACT Duplicates what harm would there be if you updated BOTH, thereby keeping the records as EXACT Duplicates?

Good Luck,
JRB-Bldr

RE: Updating only 1 record in VFP 9.0

Quote:

SELECT reinstrn Replace reinsprem WITH 33 WHERE Recno = laTop

There are two things wrong with that.

First, you can't use WHERE with REPLACE. You need to use FOR.

Second, those are two separate statements, and should therefore be on separate lines (like in the code I posted).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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!

Resources

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