Search code examples
alter-tabledb2-luw

Altering data type and default on DB2 LUW 10.5

I am trying to do 2 alters to a column in DB2 in the same alter command, and it doesn't seem to like my syntax. Is there a way to do this? If so, how?

create table tbl(col varchar(10))

The following works, using 2 alter statements:

alter table tbl
alter column col set data type varchar(128)

alter table tbl
alter column col set default current user

The following attempts fail:

alter table tbl
alter column col set data type varchar(128) set default current user

An unexpected token "set data type varchar(128)" was found following "TBL alter column col". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28 

alter table tbl
alter column col set data type varchar(128) with default current user

An unexpected token "data type varchar(128)" was found following "alter column col set". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.28 

alter table tbl
alter column col set data type varchar(128) 
alter column col set default current user

"COL" is a duplicate name.. SQLCODE=-612, SQLSTATE=42711, DRIVER=3.67.28

Solution

  • You cannot alter the same column twice in the same statement -- that's what SQLCODE -612 is telling you. As per http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00612n.html

    a column name can only be referenced in one ADD, DROP COLUMN, or ALTER COLUMN clause in a single ALTER TABLE statement.

    The other attempts are syntactically incorrect.