ALTER TABLE ALTER COLUMN RENAME Syntax

Question

Question

Oleksandr Kyrylov · Feb 2, 2022

#SQL #InterSystems IRIS

Hi everyone, i cant understand what is wrong in my query:

ALTER TABLE MyNamespace.MyTable ALTER COLUMN CurrentColumnName RENAME NewColumnName

I just want to rename column name using sql.

I could not find any life example using this syntax.

SQLCODE for this query is -25:

-25 Input encountered after end of query

Quote from documentation:

Rename the column using the syntax ALTER TABLE tablename ALTER COLUMN oldname RENAME newname. Renaming a column changes the SQL field name. It does not change the corresponding persistent class property name. ALTER COLUMN oldname RENAME newname replaces oldfield name references in trigger code and ComputeCode.'

Discussion (12)0

Log in or sign up to continue

Vitaliy Serdtsev · Feb 3, 2022

After calling

alter table fixxer.decars alter column color rename color2

it became

Property color As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 5, SqlFieldName = COLOR2 ];

It looks like something was fixed in 2021.2.

1 0

Benjamin De Boe · Feb 3, 2022

This is indeed a new piece of ALTER TABLE syntax we introduced between 2020.1 and 2021.1, so nothing wrong with your 2020.1 instance

1 0

Vitaliy Serdtsev · Feb 3, 2022

I won't be able to check for version 2020.1 since I have 2021.2.

Try to make a DROP COLUMN and then ADD COLUMN (of course according to the documentation for your version)

1 0

score 1 · Answer 1 · 2022-02-02T10:53:19-05:00

Eduard Lebedyuk · Feb 2, 2022

Can't reproduce. Can you post an example please?

Maybe you have a semicolon after your query?

1 0

score 0 · Answer 2 · 2022-02-02T11:32:09-05:00

Example:

I have table named fixxer.decars, i want to rename column "color" to "color2"

I tried to use the query above and met the error

score 0 · Answer 3 · 2022-02-02T12:42:03-05:00

Eduard Lebedyuk · Feb 2, 2022

You should contact the WRC.

0 0

score 1 · Answer 4 · 2022-02-03T02:01:49-05:00

Given (IRIS 2021.2):

Class fixxer.decars Extends %Persistent [ DdlAllowed, SqlRowIdName = UniqueIdentifier ]
{

Index iUnq On (brand, MaxSpeed, color) [ IdKey, PrimaryKey, Unique ];
Property brand [ SqlColumnNumber = 2 ];
Property MaxSpeed;
Property color;

}

USER>d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <>.
Enter , 'q' to quit, '?' for help.
[SQL]USER>>select * from fixxer.decars
1.      select * from fixxer.decars
 
UniqueIdentifier        brand   MaxSpeed        color
 
0 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0020s/5/140/0ms
          execute time(s)/globals/cmds/disk: 0.0004s/1/719/0ms
                          cached query class: %sqlcq.TEST.cls4
---------------------------------------------------------------------------
[SQL]USER>>alter table fixxer.decars alter column color rename color2
2.      alter table fixxer.decars alter column color rename color2
 
0 Rows Affected
statement prepare time(s)/globals/cmds/disk: 0.0153s/2 113/13 157/0ms
          execute time(s)/globals/cmds/disk: 0.1492s/52 206/467 790/0ms
                          cached query class: %sqlcq.TEST.cls5
---------------------------------------------------------------------------
[SQL]USER>>select * from fixxer.decars
3.      select * from fixxer.decars
 
UniqueIdentifier        brand   MaxSpeed        COLOR2
 
0 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0741s/38 529/179 560/0ms
          execute time(s)/globals/cmds/disk: 0.0003s/1/719/0ms
                          cached query class: %sqlcq.TEST.cls4
---------------------------------------------------------------------------
[SQL]USER>>

Now:

Class fixxer.decars Extends %Persistent [ DdlAllowed, SqlRowIdName = UniqueIdentifier ]
{

Index iUnq On (brand, MaxSpeed, color) [ IdKey, PrimaryKey, Unique ];
Property brand [ SqlColumnNumber = 2 ];
Property MaxSpeed;
Property color [ SqlFieldName = COLOR2 ];

}

PS: by the way Studio highlights the error in the following code

&sql(alter table fixxer.decars alter column color rename color2)

But the compilation goes without errors.

score 0 · Answer 5 · 2022-02-03T05:21:51-05:00

My IRIS version is 2020.1.

I will be grateful if you see any significant differences between my example and yours that cause this error. My Persistent class (this class created using 'CREATE TABLE'):

 // 
Class Fixxer.DEcars Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {yploskyi}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = DEcars ]
{ 
Property UniqueIdentifier As %Library.String(MAXLEN = 30) [ SqlColumnNumber = 2 ]; 
Property brand As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 3 ];
Property maxspeed As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 4 ]; 
Property color As %Library.String(MAXLEN = 500) [ SqlColumnNumber = 5 ]; 
Parameter USEEXTENTSET = 1; 
/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement. Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
}

score 0 · Answer 6 · 2022-02-03T05:40:41-05:00

I think so too. I also noticed that in IRIS 2020.1 documentation is nothing about 'ALTER COLUMN RENAME' syntax. May be there is another way to rename column name using SQL but i cant find it. Thanks for response.

score 1 · Answer 7 · 2022-02-03T05:44:20-05:00

I also noticed that in IRIS 2020.1 documentation is nothing about 'ALTER COLUMN RENAME' syntax.

Yes, check alter-column-action ::=
2020.1
2021.1

score 0 · Answer 8 · 2022-02-03T06:23:29-05:00

So, the author is not lucky and in its version this feature is not available.
It seems you have the wrong addressee, I have 2021.2 :)

score 0 · Answer 9 · 2022-02-03T05:54:34-05:00

Oleksandr Kyrylov · Feb 3, 2022

Yes, I decided to use that approach. Thank you

0 0