ALTER TABLE Testing ALTER COLUMN TestDec decimal(16,1)
Just put decimal(precision, scale) , replacing the precision and scale with your desired values.
I haven't done any testing with this with data in the table, but if you alter the precision, you would be subject to losing data if the new precision is lower.
There may be a better way, but you can always copy the column into a new column, drop it and rename the new column back to the name of the first column.
to wit:
ALTER TABLE MyTable ADD NewColumnName DECIMAL(16, 2);
GO
UPDATE MyTable
SET NewColumnName = OldColumnName;
GO
ALTER TABLE CONTRACTS DROP COLUMN OldColumnName;
GO
EXEC sp_rename
@objname = 'MyTable.NewColumnName',
@newname = 'OldColumnName',
@objtype = 'COLUMN'
GO
This was tested on SQL Server 2008 R2, but should work on SQL Server 2000+.
In my case I needed to fix a calculated column without the designer wanting to drop and create the original table, and one my column happened to be a DECIMAL(18, 2) and it refused to let me update all the records that were Null to 0.00, as there was allot of issues updating over 1 million records, and the reason I needed to update the null values to 0.00, was because there was a default set for 0.00, and it should never have ended up with a null value, but it did.
So what I had the do is quickly drop the old calculated one, and create a new calculated one, and this was the only way I could actually alter it without getting stuck with complex routines.
Hope this helps someone that eneded up in the same scenario. Now I need to figure out how that default value of 0.00 was overridden with NULL....
ALTER TABLE MyTable DROP COLUMN OldColumnName;
GO
ALTER TABLE MyTable ADD NewColumnName as AS (DecimalColumn1 * DecimalColumn1);
GO
5条答案
按热度按时间noj0wjuj1#
Just put
decimal(precision, scale)
, replacing the precision and scale with your desired values.I haven't done any testing with this with data in the table, but if you alter the precision, you would be subject to losing data if the new precision is lower.
fv2wmkja2#
There may be a better way, but you can always copy the column into a new column, drop it and rename the new column back to the name of the first column.
to wit:
This was tested on SQL Server 2008 R2, but should work on SQL Server 2000+.
aor9mmx13#
For you problem:
cwtwac6a4#
In my case I needed to fix a calculated column without the designer wanting to drop and create the original table, and one my column happened to be a DECIMAL(18, 2) and it refused to let me update all the records that were Null to 0.00, as there was allot of issues updating over 1 million records, and the reason I needed to update the null values to 0.00, was because there was a default set for 0.00, and it should never have ended up with a null value, but it did.
So what I had the do is quickly drop the old calculated one, and create a new calculated one, and this was the only way I could actually alter it without getting stuck with complex routines.
Hope this helps someone that eneded up in the same scenario. Now I need to figure out how that default value of 0.00 was overridden with NULL....
qlzsbp2j5#
In Oracle 10G and later following statement will work.
ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> <DATA_TYPE>
If the current data type is NUMBER(5,2) and you want to change it to NUMBER(10,2), following is the statement
ALTER TABLE <TABLE_NAME> MODIFY <COLUMN_NAME> NUMBER(10,2)