Update 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

General SQL Server Forums

New to SQL Server Administration

Update table

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-15 : 13:21:32

Hi,

I have a table with only one column dates listed. Then I Alter the table to add 2 additional columns:

Alter Table #Amort_36mo
Add
Fees char,
Monthly_Amort char

Finally, I want to update the table with some values and i do the following:

update #Amort_36mo
set [Fees] = 150000
where [cal_date] = '2011-01-01'
____

The command executes successfully but instead of showing the number 150000 the value that displays is an asterics (*).

Can someone help me and let me know why this happens. Also, is there another way to insert values in specific cells (not full rows or columns)? lastly, how can I insert a formula to compute the values of a column based on the corresponding rows in the table (ex: column total fees = column fees 1 + column fees 2)

Thanks !!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-15 : 14:14:54

1. You need to give a lenght for char when adding the column because the default length is 1.
2. Char is character type and you should do the update with quotes around the value i.e. '150000'.
3. cal_date should be of datatype DATETIME when you need to store a date. In SQL Server 2008 you can use the datatype DATE to have no time part.
4. To always be sure you should compare your date like this '20110101' i.e. YYYYMMDD.

No, you're never too old to Yak'n'Roll if you're too young to die.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-15 : 14:17:58

Thanks webfred! In addition, can you please tell me if I want to update multiple rows in a column, how should I do this? Currently the only way I can do that is repeat the whole update function over and over again with different values of course.

update #Amort_36mo
set Fees = 415
where [cal_date] = '2011-01-01';

update #Amort_36mo
set fees = 135
where [cal_date] = '2012-01-01'

update #Amort_36mo
set fees = 135
where [cal_date] = '2012-01-01'

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-11-24 : 01:23:29

when ever you are using amount columns go with decimal or money datatype.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources