update case statement - 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)

update case statement

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-10-26 : 10:53:01

guys,

I have situation here where I have to update role = 1 where role = 2 and update role = 2 where role = 1

ID ROLE
____________
1 1
2 2
3 1
4 2

It should be transformed to

ID ROLE
____________
1 2
2 1
3 2
4 1

I think this can be done only using case statement which doesnt seem to work for me

any suggestions/inputs would help

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 10:54:38

[code]
update table
set ROLE = case when ROLE = 1 then 2 else 1 end
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 10:59:09

update table set role = 3 - role

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 11:00:40

quote:
Originally posted by Peso

update table set role = 3 - role

Peter Larsson
Helsingborg, Sweden

Good idea

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-10-26 : 11:05:42

quote:
Originally posted by Peso

update table set role = 3 - role

Peter Larsson
Helsingborg, Sweden

great idea!!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-26 : 11:07:21

Job interview question?

I would do it this way, just in case there is some value besides 1 or 2 in ROLE.


update MyTable
set   ROLE =
	case
	when ROLE = 1
	then 2
	when ROLE = 2
	then 1
	else ROLE
	end

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 11:25:11

update table set role = 3 - role
where role in (1, 2)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources