Replace problem

[mcginty]

Hi All,

I have a query about a replace statement.. Using VFP 6. If I run a select query it retrieves the correct data:

select * from table1 where field1 = 'test' and field2 like 'A%'

however if I run a replace query with the same criteria I get an error 'Command contains unrecognized phrase/keyword':

Replace all field3 with 'abcd' where field1 = 'test' and field2 like 'A%'

What's the problem or how can I replace using like?

 

[youwannawhat]

You must use REPLACE/FOR rather than REPLACE/WHERE

 

[Eguy]

Hi;

The syntax is slightly different for REPLACE

Replace all field3 with 'abcd' FOR field1 = 'test' and field2 like 'A%'

Ed
Please let me know if the sugestion(s) I provide are helpful to you.
Sometimes you're the windshield... Sometimes you're the bug.

 

[rgbean]

The LIKE option is strictly an SQL construct. You can however use the VFP LIKE() function.

Replace all field3 with 'abcd' ;
where field1 = 'test' and LIKE(field2,'A%')

Rick

 

[rgbean]

OOPs,
I got so concerned about the LIKE, I forgot to mention the FOR rather than the WHERE clause.

Rick

 

[mcginty]

Hi Guys, Thanks for all the help, unfortunately it's still not working.

Sorry, I was using 'FOR' in my query, (typo error), and when I try using Like(field2,'A%') no records are being returned/replaced?

 

[mcginty]

Hi All, Still stuck on this, even a simple query like select * from table1 where like(field2,'A%') won't work, (and there are records beginning with A)

Ted

 

[itflash]

maybe I am being too basic, but why not use the SQL Update function?

so looking at your first question.....the sql update would be:

update table1 ;
set field3 = 'abcd' ;
where field1 = 'test' and field2 like 'A%'

 

[rgbean]

OK, I appolgize for more syntax errors - sometimes your fingers type faster than your brain can work. This DOES work:

create cursor test ;
  (field1 C(4), field2 C(4), field3 c(4))

INSERT into test values ("test","ABCD","    ")&&OK
INSERT into test values ("test","aBCD","    ")
INSERT into test values ("TEST","ABCD","    ")
INSERT into test values ("test","AXXX","    ")&&OK
INSERT into test values ("test","XABC","    ")

Replace all field3 with 'abcd' ;
  for field1 = 'test' and LIKE('A*', field2)
browse

Note: While the SQL LIKE uses % and _ for wild card characters, the VFP LIKE() function uses the DOS like * and ?.

Rick

 

[mcginty]

Thanks guys for putting me on the right track, it worked using the 'A*' before the field and with an asterisk.

Ted