March 17, 2009 at 9:21 am
can we modify more than two columns in a sql table with the alter statement?
March 17, 2009 at 9:33 am
I don't think you can.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2009 at 9:37 am
Yes, you can. I am including a partial copy of the ALTER TABLE statement from BOL:
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
| {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
}
| [ WITH { CHECK | NOCHECK } ] ADD
{
|
|
} [ ,...n ]
If you notice the [,...n] after the curly brace, that indicates you can put in multiple ALTER COLUMN statements in the ALTER TABLE.
March 17, 2009 at 9:40 am
It's not possible to ALTER more than 1 column in a single ALTER TABLE statement, you have to write 2 different alter statements.
Straight from BOL:
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
| ADD
...
So, there is no placeholder indicating that the item can be repeated n number of times.
--Ramesh
March 17, 2009 at 9:41 am
Now, i dont know the relevance. But i am tempted to ask, is it possible in Oracle?
March 17, 2009 at 9:42 am
Lynn Pettis (3/17/2009)
Yes, you can. I am including a partial copy of the ALTER TABLE statement from BOL:ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
| {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
}
| [ WITH { CHECK | NOCHECK } ] ADD
{
|
|
} [ ,...n ]
If you notice the [,...n] after the curly brace, that indicates you can put in multiple ALTER COLUMN statements in the ALTER TABLE.
Lynn, I think the placeholder is for ADD option & not for the ALTER option.
--Ramesh
March 17, 2009 at 9:45 am
Lynn, have you tried it? I just tried all of these options:
create table #T (
ID int identity primary key,
Col1 tinyint,
Col2 tinyint);
alter table #T
alter column Col1 smallint, alter column Col2 smallint;
alter table #T
alter column Col1 smallint, column Col2 smallint;
alter table #T
alter column Col1 smallint, Col2 smallint;
Every single one of those gets "Line 2: Incorrect syntax near ','." Level 15 error. Table does not get altered.
However, this does work just fine:
alter table #T
alter column Col1 smallint;
So, either I'm reading this wrong, or it doesn't work that way.
I tried all of these in 2000 and 2005. Didn't try them in 2008, since I don't have that available to me right now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2009 at 9:49 am
I'll give it a try. If it doesn't work, then I think there is a problem with the documentation. I was just reading it as they say to.
March 17, 2009 at 9:49 am
nabajyoti.b (3/17/2009)
Now, i dont know the relevance. But i am tempted to ask, is it possible in Oracle?
Yes, it's possible in Oracle. This should work:
ALTER TABLE SomeTable
MODIFY ( SomeCol1 NUMBER(3,2), SomeCol2 NUMBER(5,2) )
--Ramesh
March 17, 2009 at 9:58 am
I humbly apologize, I just came to the same conclusion that the repeating place holder is for adding new columns.
March 17, 2009 at 10:03 am
The documentation is correct - just very hard to read. Lynn - the [,...n] you saw is under the ADD and DROP sections, and not the ALTER.
That being said - there's a very straightforward way to ALTER two columns at the same time (which is what SQL Server does when you modify two columns in the UI anyway): create a new table, copy the data into it, drop the old and rename the new table to the old name.
I realize it's not as tidy as doing this within the ALTER TABLE command - but it's going to happen that way anyway....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2009 at 7:07 am
Or, you could do it the REALLY hard way, and type "Alter Table" twice. I know that's a lot of work, but it does get the job done.
alter table dbo.MyTable
alter column Col1 int;
alter table dbo.MyTable
alter column Co2 int;
Pretty rough, right? 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply