Is it possible to change the data type during an sql update statement?

Hello,

I have a SQL update statement that updates some user names, however, the user names exceed the length of the data type. Currently, for the column username the data type is set to nvarchar (8).

How can I change that to nvarchar(10) in a SQL Update statement?

Thanks in advance.

You can't put in more data than there is space to store it. If the column holds 8 characters, then you cannot put 10 characters in that same space.

You would need to change the table definition to increase the space allocated for the UserNames.

|||

Thanks for the reply...

I figured that would be the case, but what I need to do is change the data type to 10 before I can update the user names. I also tried using Alter table:

ALTER TABLE table_name
MODIFY column_name column_type;

For some reason, SQL Express did not like the MODIFY function...

Thanks in advance...

|||

Refer to Books Online, Topic: "Alter Table"

Modify is not a T-SQL keyword. You need to use ALTER.

Code Snippet

ALTER TABLE MyTable
ALTER COLUMN MyColumn varchar(10)

You might wish to consider bumping it up a bit more -just in case you later find more is required.

Here is Books Online for SQL Server Express:

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F

|||

That worked!

I greatly appreciate your response, and for providing me with the SQL links.

Thanks Arnie!

|||It does not work if MyColumn has a relationship with other table. In that case how can I do that? thanks|||

First, remove (or DROP) the PK-FK relationship,

ALTER the column,

then re-establish the relationship.