Changing the name of a field 1

[IlyaRabyy]

Colleagues,
Say, you have a table in which you need to change - programmatically! - a field's name (just that).
I can think of two ways to do that:

Method #1.

AFIELDS(laFlds, "OLD_TABLE")
change the field's name in the array
CREATE TABLE "NEW_TABLE" FROM ARRAY laFlds
APPEND FROM OLD_TABLE

Method #2.

ALTER TABLE OLD_TABLE ADD COLUMN NewFldName
REPLACE OLD_TABLE.NewFldName WITH OLD_TABLE.OldFldName ALL
ALTER TABLE OLD_TABLE DROP COLUMN OLdFldName

But!... Is there any command to do this with one line of code? (The level of my senility is definitely on the rise! :-( )

TIA!

Regards,

Ilya

 

[Mike Lewis]

Hi Ilya,

Well, as one senile to another (sorry, didn't really mean that), my first thought was to combine DROP COLUMN with ADD COLUMN in the same ALTER TABLE command. That's syntactically possible, but, of course, it would only work if the column in question was empty in every row. If not, you'd lose the contents of the column.

So, my conclusion is that you can't do it in a single command. Maybe young Olaf will have an answer?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[IlyaRabyy]

Yeah, this is what I was afraid of, Mike... (sigh!)
Oh, well, "negative result is also The Result" - ain't it so?


Regards,

Ilya

 

[GriffMG]

Couldn't you do it with a SQL query, using an AS clause?

Select flda,fldb, fldc AS fldd from myTable into cursor...

Or somesuch?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[IlyaRabyy]

Yea, Griff, I've overlooked this possibility!
However, it's still not a one command but 3...4 - just to have the new table, i.e.

SELECT OT.*, OT.OldFldName AS NewFldName FROM OLD_TABLE OT INTO CURSOR C_NEW READWRITE
ALTER TABLE C_NEW DROP COLUMN OldFldName
lcDBF = DBF("OLD_TABLE")
USE IN OLD_TABLE
COPY TO (m.lcDBF)

But then you'd lose all the indices, and that means you have to also have a piece of code with ATAGINFO() before closing the old table's alias, and then post-process this new table in the FOR...NEXT cycle with the array of indices... definitely not one command. :-(

P.S. For the record, I belog to that 10nd kind of people: I dig the binary! ;-)

Regards,

Ilya

 

[GriffMG]

True it would be a clumsy, inelegant solution.

Join the 10!

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

ALTER TABLE has the RENAME COLUMN clause, just look in the help:

RENAME COLUMN FieldName4TO FieldName5
Makes it possible for you to change the name of a field in the table. The FieldName4 parameter specifies the name of the field to be renamed. The FieldName5 paramter specifies the new name of the field.

Create Cursor curTest (Hello C(10))
Alter Table curTest Rename Column Hello To World
Browse

Bye, Olaf

 

[Mike Lewis]

ALTER TABLE has the RENAME COLUMN clause

How did I miss that?

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[IlyaRabyy]

Well, Mike, "as one senile to another" ( ;-) ) I've missed it as well... :-( ... although I suspected that some such clause might exist.
There's a warning, however, of a "watch the indices' key expressions" kind. (Why there is always "a teaspoon of tar in a barrel of honey"?)

Regards,

Ilya

 

[Mike Lewis]

Ilya, I saw the warning as well. I didn't bother to draw your attention to it, because I was sure you would already be aware of those issues.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

If you create the table new via the code you already had, you would also need to take care about your current collation sequence. Do you really use anything different than MACHINE? Even if you do, if that is the SET("COLLATION") that menas the new collation will be the old one and there should be nothing to worry about.

You can simply set up a test case and try it out, before you apply that to production data. You'll see what collation the new field name will have.

Bye, Olaf.

 

[Dan Freeman]

How did I miss that?

The same way I did.