masum billah masum billah - 1 year ago 58
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*

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**

1 name1 b
2 name2 a
3 name3 a

Condition: No CASE/SWITCH

Answer Source

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
update @t set Type = CHAR(195-ASCII(Type))

select * from @t


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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download