Write an SQL query to swap all 'f' and 'm' values
(i.e., change all 'f' values to 'm' and vice versa) with a single update
statement and no intermediate temporary tables.
table name: Salary
Solution:
update salary set sex=(case when sex='m' then 'f' else 'm' end)
Output:
SQL Script:
CREATE TABLE [dbo].[Salary](
[id][int] NOT NULL,
[name][varchar](50) NULL,
[sex][char](10) NULL,
[salary][int] NULL,
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Salary] ([id], [name], [sex], [salary]) VALUES (1, N'A', N'f ', 2500)
GO
INSERT [dbo].[Salary] ([id], [name], [sex], [salary]) VALUES (2, N'B', N'm ', 1500)
GO
INSERT [dbo].[Salary] ([id], [name], [sex], [salary]) VALUES (3, N'C', N'f ', 5500)
GO
INSERT [dbo].[Salary] ([id], [name], [sex], [salary]) VALUES (4, N'D', N'm ', 500)
GO