replace syntax

I am issuing the below syntax, and the result is it not only changes
what it is supposed to, but also records where groupid = 54

REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'

MikeA
Wed Sep 19 10:17:08 PDT 2007

It sounds like groupid is a character field and not a numeric field so both
5 and 54 start with the same characters. You can use two equal signs to
specify "exactly equal to" by doing it this way instead::

REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid == '5'

OR THIS WAY:

REPLACE ALL groupname WITH "Customer Cell Numbers" FOR int(val(groupid)) = 5

Mike


<dan@willcraft.com> wrote in message
news:qam2f3pnqhoiui4r1dsspqhcfq62qvuev8@4ax.com...
>I am issuing the below syntax, and the result is it not only changes
> what it is supposed to, but also records where groupid = 54
>
> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'

Roger
Wed Sep 19 10:38:10 PDT 2007


<dan@willcraft.com> wrote in message news:qam2f3pnqhoiui4r1dsspqhcfq62qvuev8@4ax.com...
>I am issuing the below syntax, and the result is it not only changes
> what it is supposed to, but also records where groupid = 54
>
> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'

Have a look at SET EXACT in VFP help.

-Roger

dan
Wed Sep 19 10:42:54 PDT 2007

Thank you vet much,
I am finding good success with your recommendation:
REPLACE ALL groupname WITH "Customer Cell Numbers" FOR
int(val(groupid)) = 5
Thanks again,
On Wed, 19 Sep 2007 17:17:08 GMT, "MikeA" <appell@appellsoftware.com>
wrote:

>It sounds like groupid is a character field and not a numeric field so both
>5 and 54 start with the same characters. You can use two equal signs to
>specify "exactly equal to" by doing it this way instead::
>
>REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid == '5'
>
>OR THIS WAY:
>
>REPLACE ALL groupname WITH "Customer Cell Numbers" FOR int(val(groupid)) = 5
>
>Mike
>
>
><dan@willcraft.com> wrote in message
>news:qam2f3pnqhoiui4r1dsspqhcfq62qvuev8@4ax.com...
>>I am issuing the below syntax, and the result is it not only changes
>> what it is supposed to, but also records where groupid = 54
>>
>> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'
>

Jan
Wed Sep 19 11:26:05 PDT 2007

dan@willcraft.com napsal(a):
> I am issuing the below syntax, and the result is it not only changes
> what it is supposed to, but also records where groupid = 54
>
> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'

REPLACE groupname WITH "Customer Cell Numbers" FOR Alltrim(groupid)=='5'

MikeA
Wed Sep 19 19:21:32 PDT 2007


"Jan Bucek" <bucek.jan@post.cz> wrote in message
news:%23hlFLqu%23HHA.3940@TK2MSFTNGP05.phx.gbl...
> dan@willcraft.com napsal(a):
>> I am issuing the below syntax, and the result is it not only changes
>> what it is supposed to, but also records where groupid = 54
>>
>> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'
>
> REPLACE groupname WITH "Customer Cell Numbers" FOR Alltrim(groupid)=='5'

Yep - I stand corrected on that one. But, if I may "correct you back" it's
REPLACE ALL to replace all the records. While he's at it, he can also add
to his FOR statement

REPLACE all groupname WITH "Customer Cell Numbers" FOR Alltrim(groupid)=='5'
.and. ! deleted()

if he only wants to replace the ones that have not been deleted and keep the
deleted ones as is. Probably doesn't matter though. Lots of ways to "skin
the cat" with VFP.

Thanks,
Mike

Lew
Wed Sep 19 20:07:48 PDT 2007

... actually, "all" is redundant when you have a "for" clause. You need only
use "all" to scope the entire table when there is no "for" clause.
"MikeA" <appell@appellsoftware.com> wrote in message
news:MOkIi.4496$Bm1.1478@trnddc01...
>
> "Jan Bucek" <bucek.jan@post.cz> wrote in message
> news:%23hlFLqu%23HHA.3940@TK2MSFTNGP05.phx.gbl...
>> dan@willcraft.com napsal(a):
>>> I am issuing the below syntax, and the result is it not only changes
>>> what it is supposed to, but also records where groupid = 54
>>>
>>> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'
>>
>> REPLACE groupname WITH "Customer Cell Numbers" FOR Alltrim(groupid)=='5'
>
> Yep - I stand corrected on that one. But, if I may "correct you back"
> it's REPLACE ALL to replace all the records. While he's at it, he can
> also add to his FOR statement
>
> REPLACE all groupname WITH "Customer Cell Numbers" FOR
> Alltrim(groupid)=='5' .and. ! deleted()
>
> if he only wants to replace the ones that have not been deleted and keep
> the deleted ones as is. Probably doesn't matter though. Lots of ways to
> "skin the cat" with VFP.
>
> Thanks,
> Mike
>
>

Man-wai
Thu Sep 20 05:52:31 PDT 2007

dan@willcraft.com wrote:
> I am issuing the below syntax, and the result is it not only changes
> what it is supposed to, but also records where groupid = 54

ALWAYS select the DBF/Alias first before a REPLACE. Always!

> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'


--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 20:50:01 up 9 days 11:09 1 user load average: 0.04 0.01 0.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Man-wai
Thu Sep 20 05:53:03 PDT 2007


Why not use UPDATE SQL?

> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'


--
@~@ Might, Courage, Vision, SINCERITY.
/ v \ Simplicity is Beauty! May the Force and Farce be with you!
/( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
^ ^ 20:51:01 up 9 days 11:10 1 user load average: 0.09 0.02 0.00
news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Fred
Thu Sep 20 08:14:38 PDT 2007

No, you really should use the IN clause of the REPLACE to specify the alias.

REPLACE yourfield WITH yourvalue FOR condition IN youralias

--
Fred
Microsoft Visual FoxPro MVP


"Man-wai Chang ToDie" <toylet.toylet@gmail.com> wrote in message
news:eWRlzT4%23HHA.3780@TK2MSFTNGP06.phx.gbl...
> dan@willcraft.com wrote:
>> I am issuing the below syntax, and the result is it not only changes
>> what it is supposed to, but also records where groupid = 54
>
> ALWAYS select the DBF/Alias first before a REPLACE. Always!
>
>> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'
>
>
> --
> @~@ Might, Courage, Vision, SINCERITY.
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
> ^ ^ 20:50:01 up 9 days 11:09 1 user load average: 0.04 0.01 0.00
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Beverly
Thu Sep 20 08:19:34 PDT 2007

understanding how string comparisons are achieved will be valuable down
the line.

"5" = "5 "

"5" = left("5 ",1)

"5" does not == "5 "

"5" == trim("5 ")

if you have to use numbers in character fields, in addition to Mike's
suggestion

val("5") = val("5 ")

hth,
Beverly Howard

Fred
Thu Sep 20 08:25:34 PDT 2007

Your first example is incorrect, Bev. "5 " = "5" is true, while the way
you've shown it, "5" = "5 " is false.

--
Fred
Microsoft Visual FoxPro MVP


"Beverly Howard [Ms-MVP/MobileDev]" <BevNoSpamBevHoward.com> wrote in
message news:%23JXhnm5%23HHA.3916@TK2MSFTNGP02.phx.gbl...
> understanding how string comparisons are achieved will be valuable down
> the line.
>
> "5" = "5 "
>
> "5" = left("5 ",1)
>
> "5" does not == "5 "
>
> "5" == trim("5 ")
>
> if you have to use numbers in character fields, in addition to Mike's
> suggestion
>
> val("5") = val("5 ")
>
> hth,
> Beverly Howard
>
>
>

Beverly
Thu Sep 20 10:40:43 PDT 2007

doh! dislexia reverses the equation yet again!

Thanks for the dope slap! ;-)

Cy
Fri Sep 21 21:06:26 PDT 2007

Naw, I never do that. I just use the IN clause of the replace command which
removes the need to do that.

--
Cy Welch
Senior Programmer/Analyst
MetSYS Inc.
http://www.metsysinc.com

"Man-wai Chang ToDie" <toylet.toylet@gmail.com> wrote in message
news:eWRlzT4#HHA.3780@TK2MSFTNGP06.phx.gbl...
> dan@willcraft.com wrote:
>> I am issuing the below syntax, and the result is it not only changes
>> what it is supposed to, but also records where groupid = 54
>
> ALWAYS select the DBF/Alias first before a REPLACE. Always!
>
>> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'
>
>
> --
> @~@ Might, Courage, Vision, SINCERITY.
> / v \ Simplicity is Beauty! May the Force and Farce be with you!
> /( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
> ^ ^ 20:50:01 up 9 days 11:09 1 user load average: 0.04 0.01 0.00
> news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk

Tom
Sun Sep 23 06:11:43 PDT 2007

On Sep 22, 12:06 am, "Cy Welch" <cywe...@hotmail.com> wrote:
> Naw, I never do that. I just use the IN clause of the replace command which
> removes the need to do that.
>
> --
> Cy Welch
> Senior Programmer/Analyst
> MetSYS Inc.http://www.metsysinc.com
>
> "Man-wai Chang ToDie" <toylet.toy...@gmail.com> wrote in messagenews:eWRlzT4#HHA.3780@TK2MSFTNGP06.phx.gbl...
>
>
>
> > d...@willcraft.com wrote:
> >> I am issuing the below syntax, and the result is it not only changes
> >> what it is supposed to, but also records where groupid = 54
>
> > ALWAYS select the DBF/Alias first before a REPLACE. Always!
>
> >> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'
>
> > --
> > @~@ Might, Courage, Vision, SINCERITY.
> > / v \ Simplicity is Beauty! May the Force and Farce be with you!
> > /( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
> > ^ ^ 20:50:01 up 9 days 11:09 1 user load average: 0.04 0.01 0.00
> > news://news.3home.net news://news.hkpcug.org news://news.newsgroup.com.hk- Hide quoted text -
>
> - Show quoted text -

The real problem is SET EXACT. In my opinion, you should always run
with SET EXACT ON. It makes VFP comparisons like they are in virtually
every other normal language. My users would be horrified if they did
a select (I let them enter expressions in certain places) and found
that "BIGGER"="BIG". The old SET EXACT OFF is a throwback to a
bizarre feature of DBASE which is best forgotten.

The problem with REPLACE... FOR LEFT(groupid,1)="5" --- as well as
the some of the other suggestions --- is that they will not be
optimized. Just SET EXACT ON. But beware, you need to review and
maybe retest all your existing code. You may even find it FIXES old
bugs you didn't know you had.

Cy
Mon Sep 24 21:16:42 PDT 2007

SET EXACT has exactly NO affect on SQL queries. That gives you two settings
that you have to make sure are set the right way, and frankly most of my (my
employers) customers actually like the ability to specify either way. Also
as I recall the exact settings are NOT optimizable. IIRC using == in a
query or filter precludes rushmore for that portion of the query/filter. I
would guess that set exact or set ansi on would have the same affect. There
is also the fact that VFP's query syntax is different than most other
products anyways since is doesn't use the same way of checking for values
within a string. Frankly you should train your users to use the proper
syntax, since that gives them more flexibility as they can either look for
"starts with" meaning single = and "exactly matching" meaning double =.
With set exact, that is not even a possibility.

--
Cy Welch
Senior Programmer/Analyst
MetSYS Inc.
http://www.metsysinc.com

"Tom Green" <bigband@preville.qc.ca> wrote in message
news:1190553103.738253.190360@n39g2000hsh.googlegroups.com...
> On Sep 22, 12:06 am, "Cy Welch" <cywe...@hotmail.com> wrote:
>> Naw, I never do that. I just use the IN clause of the replace command
>> which
>> removes the need to do that.
>>
>> --
>> Cy Welch
>> Senior Programmer/Analyst
>> MetSYS Inc.http://www.metsysinc.com
>>
>> "Man-wai Chang ToDie" <toylet.toy...@gmail.com> wrote in
>> messagenews:eWRlzT4#HHA.3780@TK2MSFTNGP06.phx.gbl...
>>
>>
>>
>> > d...@willcraft.com wrote:
>> >> I am issuing the below syntax, and the result is it not only changes
>> >> what it is supposed to, but also records where groupid = 54
>>
>> > ALWAYS select the DBF/Alias first before a REPLACE. Always!
>>
>> >> REPLACE ALL groupname WITH "Customer Cell Numbers" FOR groupid = '5'
>>
>> > --
>> > @~@ Might, Courage, Vision, SINCERITY.
>> > / v \ Simplicity is Beauty! May the Force and Farce be with you!
>> > /( _ )\ (Xubuntu 7.04) Linux 2.6.22.6
>> > ^ ^ 20:50:01 up 9 days 11:09 1 user load average: 0.04 0.01 0.00
>> > news://news.3home.net news://news.hkpcug.org
>> > news://news.newsgroup.com.hk- Hide quoted text -
>>
>> - Show quoted text -
>
> The real problem is SET EXACT. In my opinion, you should always run
> with SET EXACT ON. It makes VFP comparisons like they are in virtually
> every other normal language. My users would be horrified if they did
> a select (I let them enter expressions in certain places) and found
> that "BIGGER"="BIG". The old SET EXACT OFF is a throwback to a
> bizarre feature of DBASE which is best forgotten.
>
> The problem with REPLACE... FOR LEFT(groupid,1)="5" --- as well as
> the some of the other suggestions --- is that they will not be
> optimized. Just SET EXACT ON. But beware, you need to review and
> maybe retest all your existing code. You may even find it FIXES old
> bugs you didn't know you had.
>

Anders
Tue Sep 25 03:01:58 PDT 2007

Hi Cy
== is optimizable. A simple test with sys(3054,11) will show it. SET ANSI
ON will have the same effect. And so will using LIKE 'string' instead of =
or ==.
So there are several ways of forcing exact equality searches.
-Anders

"Cy Welch" <cywelch@hotmail.com> wrote in message
news:2649A8C5-243B-406F-84BB-1546ACD021C8@microsoft.com...
> SET EXACT has exactly NO affect on SQL queries. That gives you two
> settings that you have to make sure are set the right way, and frankly
> most of my (my employers) customers actually like the ability to specify
> either way. Also as I recall the exact settings are NOT optimizable.
> IIRC using == in a query or filter precludes rushmore for that portion of
> the query/filter. I would guess that set exact or set ansi on would have
> the same affect. There is also the fact that VFP's query syntax is
> different than most other products anyways since is doesn't use the same
> way of checking for values within a string. Frankly you should train your
> users to use the proper syntax, since that gives them more flexibility as
> they can either look for "starts with" meaning single = and "exactly
> matching" meaning double =. With set exact, that is not even a
> possibility.
>
>>

Roger
Tue Sep 25 08:33:55 PDT 2007


"Cy Welch" <cywelch@hotmail.com> wrote:

> SET EXACT has exactly NO affect on SQL queries.

Correct, however I thought the OP was asking about REPLACE,
which has NOTHING to do with SQL but EVERYTHING to do with
EXACT. Which is why I made the observation 5 days ago.

-Roger

Cy
Tue Sep 25 16:43:03 PDT 2007

Ok, I stand corrected on the == and set exact being optimizable. That said,
as far as I know outside of a query, with set exact on there is no way to do
a "starts with" search without jumping through hoops that would include
either indexes on the exact search (doing a search on left()) or losing
optimization. Frankly I have found that turning either of those settings on
cause more trouble than their worth since as mentioned == always goes for an
exact match.

--
Cy Welch
Senior Programmer/Analyst
MetSYS Inc.
http://www.metsysinc.com

"Anders Altberg" <anders.altberg> wrote in message
news:eqxCms1$HHA.484@TK2MSFTNGP06.phx.gbl...
> Hi Cy
> == is optimizable. A simple test with sys(3054,11) will show it. SET ANSI
> ON will have the same effect. And so will using LIKE 'string' instead of =
> or ==.
> So there are several ways of forcing exact equality searches.
> -Anders
>
> "Cy Welch" <cywelch@hotmail.com> wrote in message
> news:2649A8C5-243B-406F-84BB-1546ACD021C8@microsoft.com...
>> SET EXACT has exactly NO affect on SQL queries. That gives you two
>> settings that you have to make sure are set the right way, and frankly
>> most of my (my employers) customers actually like the ability to specify
>> either way. Also as I recall the exact settings are NOT optimizable.
>> IIRC using == in a query or filter precludes rushmore for that portion of
>> the query/filter. I would guess that set exact or set ansi on would have
>> the same affect. There is also the fact that VFP's query syntax is
>> different than most other products anyways since is doesn't use the same
>> way of checking for values within a string. Frankly you should train
>> your
>> users to use the proper syntax, since that gives them more flexibility as
>> they can either look for "starts with" meaning single = and "exactly
>> matching" meaning double =. With set exact, that is not even a
>> possibility.
>>
>>>
>
>
>

Cy
Tue Sep 25 16:45:31 PDT 2007

Agreed, but I still believe that == makes more sense there than SET EXACT
since that allows for both begins with searches and exact searches. I have
found however that there are times where setting exact ON actually makes
life easier as long as you don't forget to set it back.

--
Cy Welch
Senior Programmer/Analyst
MetSYS Inc.
http://www.metsysinc.com

"Roger Ansell" <notmy@realemailaddress.net> wrote in message
news:Otzchi4$HHA.4200@TK2MSFTNGP04.phx.gbl...
>
> "Cy Welch" <cywelch@hotmail.com> wrote:
>
>> SET EXACT has exactly NO affect on SQL queries.
>
> Correct, however I thought the OP was asking about REPLACE,
> which has NOTHING to do with SQL but EVERYTHING to do with
> EXACT. Which is why I made the observation 5 days ago.
>
> -Roger
>
>
>
>

Anders
Wed Sep 26 04:46:40 PDT 2007

FOR INLIST('XX*', fieldname) is likely just as optimizable as WHERE
fieldname LIKE 'XX%', since VFP9. However it's not to see with SYS(3054)
which only works with SQL SELECT commands. I think SQL LIKE is mapped to
Xbase LIKE(), even though SQL's IN function is nolonger mapped to the
INLIST() xbase function in VFP9.
It works the same way with EXACT ON or OFF.
-Anders


"Cy Welch" <cywelch@hotmail.com> wrote in message
news:47251BD8-0AFD-42A8-855C-0B29B0E2375C@microsoft.com...
> Ok, I stand corrected on the == and set exact being optimizable. That
> said, as far as I know outside of a query, with set exact on there is no
> way to do a "starts with" search without jumping through hoops that would
> include either indexes on the exact search (doing a search on left()) or
> losing optimization. Frankly I have found that turning either of those
> settings on cause more trouble than their worth since as mentioned ==
> always goes for an exact match.
>
> --
> Cy Welch
> Senior Programmer/Analyst
> MetSYS Inc.
> http://www.metsysinc.com
>
> "Anders Altberg" <anders.altberg> wrote in message
> news:eqxCms1$HHA.484@TK2MSFTNGP06.phx.gbl...
>> Hi Cy
>> == is optimizable. A simple test with sys(3054,11) will show it. SET ANSI
>> ON will have the same effect. And so will using LIKE 'string' instead of
>> = or ==.
>> So there are several ways of forcing exact equality searches.
>> -Anders
>>
>> "Cy Welch" <cywelch@hotmail.com> wrote in message
>> news:2649A8C5-243B-406F-84BB-1546ACD021C8@microsoft.com...
>>> SET EXACT has exactly NO affect on SQL queries. That gives you two
>>> settings that you have to make sure are set the right way, and frankly
>>> most of my (my employers) customers actually like the ability to specify
>>> either way. Also as I recall the exact settings are NOT optimizable.
>>> IIRC using == in a query or filter precludes rushmore for that portion
>>> of
>>> the query/filter. I would guess that set exact or set ansi on would
>>> have
>>> the same affect. There is also the fact that VFP's query syntax is
>>> different than most other products anyways since is doesn't use the same
>>> way of checking for values within a string. Frankly you should train
>>> your
>>> users to use the proper syntax, since that gives them more flexibility
>>> as
>>> they can either look for "starts with" meaning single = and "exactly
>>> matching" meaning double =. With set exact, that is not even a
>>> possibility.
>>>
>>>>
>>
>>
>>

Lew
Wed Sep 26 19:30:55 PDT 2007

Sorry for kibitzing, but does inlist() now support wildcards?

"Anders Altberg" <anders.altberg> wrote in message
news:%23vYWdLDAIHA.484@TK2MSFTNGP06.phx.gbl...
> FOR INLIST('XX*', fieldname)

Anders
Thu Sep 27 04:48:26 PDT 2007

Nope. My bad.
I probably meant LIKE('XX*', filedname)
-Anders

"Lew" <lew@fastmail.fm> wrote in message
news:eMP7z5KAIHA.5360@TK2MSFTNGP03.phx.gbl...
> Sorry for kibitzing, but does inlist() now support wildcards?
>
> "Anders Altberg" <anders.altberg> wrote in message
> news:%23vYWdLDAIHA.484@TK2MSFTNGP06.phx.gbl...
>> FOR INLIST('XX*', fieldname)
>
>