ALTER table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ALTER table

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-28 : 18:37:36

I want to alter a table column.
This table column is of type int and size 4.
I want to get the same table but with the numbers of this column divided by 100.
Is this possible?
Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-28 : 18:40:36

you want to change the table's column definition or change the content of a column ?

KH
[spoiler]Time is always against us[/spoiler]

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-28 : 18:43:28

I just want to change the nuumbers of the column.
For examle in a field of the column it reads now 360 I want it to read 3.60

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-28 : 18:43:37

Any reason this would not work:
Select MyColumn/100 from MyTable

CODO ERGO SUM

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-28 : 18:57:15

Should I create a view or can I alter the table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-28 : 20:39:56

You can use a view or select statement as shown by Michael.

If you want to permanently change that, then alter the column to a decimal data type and then use UPDATE statement to change it


alter table yourtable alter column yourcolumn decimal(10,2)

update yourtable set yourcolumn = yourcolumn / 10

KH
[spoiler]Time is always against us[/spoiler]

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-29 : 01:36:44

I used this code :

CREATE VIEW view_LABSTORE AS

(

SELECT REC_TIME AS REC_TIME , FO_DAY AS FO_DAY , TOTAL/100 AS TOTAL
FROM posuser.LABSTORE
)

but I am loosing a digit because of the division.
For example in a field that it has the value 150 in the table I get after the division 1 and not 1.5
Any idea how to solve this one?
Thnx a lot guys for helping!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 01:43:00

quote:
Originally posted by skiabox

I used this code :

CREATE VIEW view_LABSTORE AS

(

SELECT REC_TIME AS REC_TIME , FO_DAY AS FO_DAY , TOTAL/100.0 AS TOTAL
FROM posuser.LABSTORE
)

but I am loosing a digit because of the division.
For example in a field that it has the value 150 in the table I get after the division 1 and not 1.5
Any idea how to solve this one?
Thnx a lot guys for helping!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 06:08:41

quote:
Originally posted by Michael Valentine Jones

Any reason this would not work:
Select MyColumn/100 from MyTable

CODO ERGO SUM

Becuase of implicit convertion

SELECT 360/100

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources