An idea to write an article came to me yesterday when one of my friends encountered a question in an interview. I felt that it would be a good brain teaser.

Question: Swap the value of s specific column value with another. For example, if we have a table t1 having column Gender then Male should be replaced with Female and vice versa.

Note: You should have a little knowledge of Cursors and the Switch Statement in SQL Server2005, 2008 and so on.

I have taken an idea and created a table keeping the structure as in the following:

  1. select * from customers
table

Here we will swap the values in the name column, like “Sachin” will be replaced by “dotnetpiper.com” and vice versa.

The output will be like the following:
Swap value

I have used a cursor to do it. The reason to choose a cursor is we will fetch each row individually and perform the desired action. Here is the actual SQL query implementation:
  1. DECLARE @name VARCHAR(50) -- database name
  2. DECLARE DotnetPiper_Cursor CURSOR FOR
  3. SELECT name
  4. FROM customers
  6. OPEN DotnetPiper_Cursor
  7. FETCH NEXT FROM DotnetPiper_Cursor INTO @name
  9. WHILE @@FETCH_STATUS = 0
  10. BEGIN
  11. Update customers SET name=( Case when @name='sachin' then 'dotnetpiper.com'
  12. when @name= 'dotnetpiper.com' then 'sachin'
  13. else @name
  14. End) WHERE CURRENT OF DotnetPiper_Cursor
  16. FETCH NEXT FROM DotnetPiper_Cursor INTO @name
  17. END
  19. CLOSE DotnetPiper_Cursor
  20. DEALLOCATE DotnetPiper_Cursor
Approach 2
  1. select * from customers
  2. update customers set name = (case name when 'sachin' then 'dotnetpiper'
  3. else 'sachin' end);
Query

SQL Snippet to create table Customer table:
  1. USE [Employee]
  2. GO
  4. /****** Object: Table [dbo].[Customers] Script Date: 08/03/2015 07:18:12 ******/
  5. SET ANSI_NULLS ON
  6. GO
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  11. SET ANSI_PADDING ON
  12. GO
  14. CREATE TABLE [dbo].[Customers](
  15. [ID] [int] NULL,
  16. [Name] [varchar](50) NULL,
  17. [Salary] [varchar](50) NULL
  18. ) ON [PRIMARY]
  20. GO
  22. SET ANSI_PADDING OFF
  23. GO