modifying table

[joseph xavier]

I am using mysql database. I tried to modify the table using the statement "alter table abc modify(CustName varchar(35) not null, CustAddr1 varchar(50));" . But it gives me an error. However it allows me to modify a single feild with this statement-"alter table abc modify CustName varchar(35) not null ;" . Again note that it is without the brackets. If I use brackets as this - "alter table abc modify (CustName varchar(35) not null );" . it again gives me errors...

can anybody let me know why mutliple feilds cannot be modified with the same statement...

[maha anna]

josxavier,'
You can refer to ALTER TABLE mySql sytax here.
http://www.mysql.com/doc/A/L/ALTER_TABLE.html
It looks like, we can't modify multiple cols with one 'modify' clause, but we can use multiple 'modify ...' in one 'ALTER TABLE...' statement.
ALso please re-register with first and last names. Thank you.
regds
maha anna

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  orADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col
  or    table_options

[jos xavier]

Hi Mahalaxmi,
Thanks for your reply....and i have re-registered in the name of Jos Xavier....Again I went through a book on oracle (Oracle 8i complete reference) to modify multiple coloumns of a table. I experienced the same problem while using db2 . Is it a speciality of oracle to allow modification of multiple coloumns in a single statement.. I am quoting the exact statement that is mentioned in the book...
"alter table TROUBLE add (
Condition VARCHAR2(9) NOT NULL,
Wind NUMBER(3)
);"
I would be highly obliged if you could reply to my doubt..
Thanks once again
bye
best regards
joseph

Originally posted by maha anna:
[B]josxavier,'
You can refer to ALTER TABLE mySql sytax here.
http://www.mysql.com/doc/A/L/ALTER_TABLE.html
It looks like, we can't modify multiple cols with one 'modify' clause, but we can use multiple 'modify ...' in one 'ALTER TABLE...' statement.
ALso please re-register with first and last names. Thank you.
regds
maha anna

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  orADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col
  or    table_options

[/B]

[jos xavier]

Hi Mahalaxmi,
Thanks for your reply....and i have re-registered in the name of Jos Xavier....Again I went through a book on oracle (Oracle 8i complete reference) to modify multiple coloumns of a table. I experienced the same problem while using db2 . Is it a speciality of oracle to allow modification of multiple coloumns in a single statement.. I am quoting the exact statement that is mentioned in the book...
"alter table TROUBLE add (
Condition VARCHAR2(9) NOT NULL,
Wind NUMBER(3)
);"
I would be highly obliged if you could reply to my doubt..
Thanks once again
bye
best regards
joseph

Originally posted by maha anna:
[B]josxavier,'
You can refer to ALTER TABLE mySql sytax here.
http://www.mysql.com/doc/A/L/ALTER_TABLE.html
It looks like, we can't modify multiple cols with one 'modify' clause, but we can use multiple 'modify ...' in one 'ALTER TABLE...' statement.
ALso please re-register with first and last names. Thank you.
regds
maha anna

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  orADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col
  or    table_options

[/B]

[maha anna]

Hi Joseph,
Yes. 'Multiple col change' seems to be Oracle extension.
I got this information from this link http://www.mysql.com/doc/A/L/ALTER_TABLE.html. So it may not be standard SQL which is supported by many database vendors.

MODIFY is an Oracle extension to ALTER TABLE. 
TRUNCATE is an Oracle extension. See section 7.18 TRUNCATE Syntax. 

regds
maha anna