Column update - SQL UPDATE statement-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Should do the trick on SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;