update field - add more data after last character

I know how to use the Update command to update a field with new information, but what if I want to add info to the field after the last character.

For Example, I have a string field were we store email addresses.  I want to update a this field for a bunch of records, but I don't want to overwrite the data, instead I need to add to it at the end of the field.  The existing data for each record will be different, so is important to leave the existing data alone and just add to it.

modifiy the current email field from this:
investor.i_email = me@email.com

to this:
investor.i_email = me@email.com; you@email.com

I know that for updating this field I would use the following command line argument:
UPDATE investor set i_email="you@email.com" WHERE taxid='11-1111111'

My guess is that to add to this field it would be just as simple but with a different command?

I tried the following but it did not work:
UPDATE investor set i_email = i_email + ';info@mycompany.com' WHERE taxid='11-1111111'

Who is Participating?
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
UPDATE investor set i_email = ALLTRIM(i_email) + '; ' + info@mycompany.com' WHERE taxid='11-1111111

it is keeping the spaces at the end. You need to trim the field.
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
CREATE CURSOR test (name C(50))
REPLACE name WITH 'Cyril'
UPDATE test SET name = 'Captain ' + name WHERE .T.
Olaf DoschkeSoftware DeveloperCommented:
If you intend to write C(100) into a c(50) field for example, that's only possible by ALTER TABLE and extending field lengths. The only fields allowing any length by default are memo.

If you want to use the spaces you still have free in an email field, you can do that as Cyril dhows in his second answer.

Just remember, field+suffix will be len(field)+len(suffix), therefore the suffix never will appear in the field, as it's cut off, because it extends the string after the spaces, which are also already in a field. Even if you visually only see 20 chars of a 50 char length field used it's always padded to the right with spaces, so you need Alltrim or Rtrim at least to add something in the ramaining area of right spaces unused.

Longwinding explanation shortened: I'd not do this at all, but add another field for new data. Don't misuse some left over space to store data, that should go into a new field. You will face problems most probably with one mail address leaving too less space to add your further data. And would need to change all code using the email field to only take the left part of it, if the concatenated data does not belong to the mail address, this is just breaking the application.

If you're not allowed to change database, then either add new tables 1:1 related or put these even into a new database to leave the old one alone.

Bye, Olaf.
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

I agree everything written above I would just add two points:

1) If you use - (minus) operator then it will also work:
UPDATE investor set i_email = i_email - ';info@mycompany.com' WHERE taxid='11-1111111'

2) I would even recommend to have a separate table for e-mail addresses. It will have just two fields: InvestorID, and i_Email and it can collect different number of e-mails for every investor (suppose InvestorID is the primary key in Investor table). This data model is much more flexible than to add new fields to the existing table if there is not enough space...
IO_DorkAuthor Commented:
I am a little confussed.  Just to make sure we are on the same page let me clearify what I am looking for.

i have a string field where we enter email addresses.  I have a client that has 200 accounts with us, therefore I have 200 account records in foxpro for this client.  They requested us to update their account records with an additional email accross all 200 accounts.  Each account email field varies in content for this client, but the email we need to add will be amended to all accounts.  My Front end system does not allow for bulk updating of this string field accross multiple accounts, so i need to do through the backend in foxpro and use a statement in foxpro to update these 200 accounts.  I don't want to overwrite the contents, just add to them.
Also the syntax that you use will depend on where your data table 'lives'.

If this is being done in a VFP data table, then all of the VFP syntax would be applicable.

But if this change were being done in a data table 'living' in another data backend, such as SQL Server, then you would have to use syntax compatible with that backend.

For Example:
    Functions like ALLTRIM() do not exist, as such, in SQL Server
    You would need to use TRIM()

Good Luck
IO_DorkAuthor Commented:
its in a vfp dbf free table.
IO_DorkAuthor Commented:
the email field is just a stringfield allowing upto 120 characters.
UPDATE investor set i_email = i_email - ';info@mycompany.com' WHERE taxid='11-1111111'
will work for you.

You may also test if there is enough space in the field:
SELECT * FROM investor WHERE 120 - LEN(TRIM(i_email)) < LEN(';info@mycompany.com')
If it displays no rows then everything is OK.
IO_DorkAuthor Commented:
Thanks CaptainCyril and thanks to all who contributed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.