How to update multiple column definition in single update statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to update multiple column definition in...

How to update multiple column definition in single update statement

Rate Topic

Display Mode

Topic Options

Author

Message

akhandels

Posted Thursday, December 30, 2010 7:23 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 2:56 PM
Points: 23, Visits: 117

Hi All,
Does anybody know if there is a way to update column definition of multiple columns in a single ALTER statement in SQL Server 2008 or Denali CTP 1 ?
I am aware it is not possible in SQL Server 2005.

Thank You

Post #1040888

parthi-1705

Posted Thursday, December 30, 2010 7:59 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195

Can you elaborate in better
can you have some examples for it like how with scenarios

Thanks
Parthi

Thanks
Parthi

Post #1040911

GSquared

Posted Thursday, December 30, 2010 8:21 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

I don't think you can do it in SQL 2008 R2 either. Not sure about Denali.

- 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

Post #1040926

akhandels

Posted Thursday, December 30, 2010 8:28 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 2:56 PM
Points: 23, Visits: 117

I need to alter definition of two columns in a table and its being done like this :

ALTER TABLE dbo.JURISDICTION_FIELD
ALTER COLUMN FIELD_CODE NVARCHAR(60) NOT NULL;

ALTER TABLE dbo.JURISDICTION_FIELD
ALTER COLUMN [DESCRIPTION] NVARCHAR(2000) NULL;

If I need to update 20 columns in a table, there will need to be 20 ALTER TABLE statements. Is there a way to update all 20 columns using a single ALTER TABLE statement?

Please let me know if you need more clarifications.

Thank You.

Post #1040935

GSquared

Posted Thursday, December 30, 2010 8:33 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

I don't believe there is a way to do that. You can add multiple columns in a single command, but not alter multiple columns all at once.

Copy-and-paste can remove most of the work with that. Might be even simpler if you're modifying a predictable set of columns, and you query sys.columns to build the basic strings.

select 'alter table [' + object_name(object_id) + '] alter column [' + name + ']' 
from sys.columns
where object_id = ...;

Something like that.

- 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

Post #1040938

« Prev Topic | Next Topic »

Permissions