masum billah masum billah - 2 months ago 7
SQL Question

How to update database column values in a single command(no CASE/SWITCH)?

Lets say we have a table name Swap-Table.

*Input Table*

ID NAME Type
------------------
1 name1 a
2 name2 b
3 name3 b


I want to write a single command to update the table.The output table would be

**Output Table**

ID NAME Type
------------------
1 name1 b
2 name2 a
3 name3 a


Condition: No CASE/SWITCH

Answer

I only offer this as a "cute" way to do this transformation, rather than anything I'd allow (or even recommend) in production code:

declare @t table (ID int not null,Name varchar(17) not null,Type varchar(3) not null)
insert into @t(ID,NAME,Type) values
(1,'name1','a'),
(2,'name2','b'),
(3,'name3','b')
update @t set Type = CHAR(195-ASCII(Type))

select * from @t

Produces:

ID          Name              Type
----------- ----------------- ----
1           name1             b
2           name2             a
3           name3             a

(Different database products may have different ways to transform from/to ascii codes and for table variables)