Column update – SQL UPDATE statement

I have a column where it stores a reference
the reference is usually 30 alphanumeric values
The column is set as an nvarchar (30).

Id like to update this to nvarchar (5), and update all the values currently stored in this column..

is there a way to do this using update?

>Solution :

First update the table so that you truncate all the column values after the 5th character:

UPDATE tablename SET columnname = LEFT(columnname, 5);

Note, that this may not work if there are constraints on the column (like a unique constraint or foreign key references).

Then change the data type of the column:

ALTER TABLE tablename ALTER COLUMN columnname nvarchar(5);

See a simplified demo.

Leave a Reply