update query problem - 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

SQL Server Development (2000)

update query problem

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-11 : 18:34:26

I have a column of type smallint which has numbers stored in it 1,2,3,0 etc. etc.

Now, I need to convert that column to of type 'varchar' where I need to replace numbers with two string values 'Present' and 'Not Present' depending upon this condition.
if column >=1
set column = 'Present'
if columns =0
set column = 'Not Present'.

I am using SQL Server 7.0 and I tried two different update query which obviously don't work.

alter [dbo].[test] alter column tColumn varchar(12) null

update [dbo].[test] set tColumn = 'Present' where cast(tColumn as smallint) >= 1
update [dbo].[test] set tColumn = 'Not reported' where cast(tColumn as smallint) = 0 or tColumn = null

whats the best way to do this??

thanks in advance.
ujjaval.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-01-11 : 20:09:49

Personally, instead of immediately replacing the column, I would first add another column to the table to hold the 'Present' and 'Not Present' values. Update this new column as you would expect:

UPDATE test SET NewColumn = 'Present' WHERE tColumn >= 1
UPDATE test SET NewColumn = 'Not Present' WHERE tColumn = 0 OR tColumn IS NULL

This way, you can double-check the update to make sure the values are in sync. If it looks OK, then drop tColumn.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-11 : 21:28:06

[code]update [dbo].[test]
set
tColumn =
case
when cast(tColumn as smallint) >= 1
then 'Present'
else 'Not reported'
end

[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 02:34:39

or simply show them in the select statement without updation

Select columns,case when cast(tColumn as smallint) >= 1 then 'Present' else 'Not reported' end from yourTable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources