Update field

[LBC2]

I would like to create a sql statement to update one field with the first 5 digits from another field.

for examople;
Column1: 'This is a test'
Column2 would be 'This '

If I was able to do this in Access, I have problem to do this directly from the Pervasive SQL prompt.
Any help or suggestion would be appreciated.
Thanks.

 

[mirtheil]

What have you tried in Pervasive? You can use the LEFT function in your UPDATE statement. Something like:

create table tt (f1 char(100), f2 char(5));
insert into tt (F1) values ('1234567890');
insert into tt (F1) values ('0987654321');
update tt set f2 = left(f1,5);
select * from tt

I used PSQL v11 as that's what I've got.



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[LBC2]

Thanks, i see.
Actually I have 2 tables and I need to get some values from one table and populate a smaler field in the other table.
This is the update portion where I have a problem.
I will try the UPDATE portion .
Thanks.

 

[LBC2]

This is the SQL statement where I am having problems

UPDATE TABLE1 INNER JOIN TABLE2 ON TABLE1.VendorNum = TABLE2.VendorNum SET TABLE2.ShortName = LEFT(TABLE1.Name,5) WHERE TABLE2.ShortName='';

I get the following error message:
<<<<<<<<<<<<<<<<<<<<<<<<
[LNA][Pervasive][ODBC Engine Interface]A table or view name must be specified for update.
>>>>>>>>>>>>>>>>>>>>>>>>

So if anyone can help I would appreciate.

I am trying this in Pervasive PSQL windows/

Thanks

 

[mirtheil]

What version of PSQL are you using?
I don't see anything wrong with the statement. I'll try it in my test environment a little later.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[LBC2]

Thanks to check.
I am using the Pervasive 11 and I believe thePSQL is version 11.10.042.000 as indicated in the 'About' windows.