Can a IF be embedded in an UPDATE statement

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)

Can a IF be embedded in an UPDATE statement

Author

Topic

frank2
Starting Member

35 Posts

Posted - 2005-09-21 : 13:41:26

I have a table with 5 rows 2 columns.

Col 1 – CheckCode, type bit
Col 2 – MyValue, type varchar(5)

Is there a way to perform a single UPDATE that looks at the value of CheckCode and if it is 1 set MyValue to ‘YES’, if 0 set MyValue to ‘NO’?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-21 : 13:42:31

update table
set col1 = case when checkcode = 1 then 'yes' else 'no' end

Go with the flow & have fun! Else fight the flow

frank2
Starting Member

35 Posts

Posted - 2005-09-21 : 14:08:11

thanks :)

frank2
Starting Member

35 Posts

Posted - 2005-09-21 : 15:15:08

Can a filter be put on the statement? the following syntax fails.

update table
set col1 = case when checkcode = 1 then 'yes' else 'no' end
where col3 = 'yes'

Kristen
Test

22859 Posts

Posted - 2005-09-21 : 15:37:04

I would suggest this syntax:


update U
set col1 = case when checkcode = 1 then 'yes' else 'no' end
FROM table AS U
where col3 = 'yes'

although there are other acceptable alternatives. This is my preference as it is easy to add JOINs etc. as & when needed in the future

Kristen

frank2
Starting Member

35 Posts

Posted - 2005-09-21 : 16:37:00

great thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources