Updating only 1 record in VFP 9.0

[shawnwmorris]

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?

 

[JRB-Bldr]

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.

update the bottom or top record of a group of records

TOP being WHERE RECNO() = 1 and BOTTOM being WHERE RECNO() = RECCOUNT()

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

 

[Tore Bleken]

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.

 

[Mike Lewis]

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:

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

 

[shawnwmorris]

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

 

[Tore Bleken]

[pre]Update reinstrn set reinsprem = 33 WHERE Recno() = laTop[/pre]

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

 

[shawnwmorris]

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

 

[Tore Bleken]

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.

 

[JRB-Bldr]

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

 

[Mike Lewis]

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