Database

Mycroft Holmes wrote:

You cannot do it

BEGIN TRANSACTION

CREATE TABLE Person
(
    Name VARCHAR(MAX)
   ,Gender CHAR(1)
)
INSERT INTO Person VALUES
  ('Pete', 'M')
 ,('John', 'M')
 ,('Mary', 'F')
 ,('Dude', 'M')
 ,('Mary', 'F')
 
 SELECT *
   FROM Person
   
 UPDATE Person
    SET Gender = CASE Gender WHEN 'M' THEN 'F'
                                      ELSE 'M'
                                      END
                                    
 SELECT *
   FROM Person
   
ROLLBACK 

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

-- Multiple Update using Joins

-- Create temp table for Teste Update Statement
CREATE TABLE EmpDtls(Code VARCHAR(10), Name VARCHAR(100), DeptCode TINYINT)
CREATE TABLE DeptDtls(DeptCode TINYINT, DeptName VARCHAR(50), EmpCount INT)

-- Insert Sample Values
INSERT INTO EmpDtls(Code, Name, DeptCode)
VALUES('Emp1','Venkat', 1), ('Emp2','Prabu', 2), ('Emp3','Kumar', 1), ('Emp4','Karthick', 3),
('Emp5','Amith', 2),('Emp6','HariKrishna', 2)

INSERT INTO DeptDtls(DeptCode, DeptName)
VALUES(1, 'IT'),(2, 'Sales'), (3,'HR'), (4, 'Accounts')

-- Check the sample values
SELECT Code, Name, DeptCode FROM EmpDtls
SELECT DeptCode, DeptName,EmpCount FROM DeptDtls

-- Update Employees Count
UPDATE D SET D.EmpCount= E.EmpCount
FROM DeptDtls D
INNER JOIN (SELECT DeptCode, COUNT(Code) 'EmpCount' FROM EmpDtls GROUP BY DeptCode) E ON E.DeptCode=D.DeptCode

SELECT DeptCode, DeptName,EmpCount FROM DeptDtls

-- Drop Table 
IF OBJECT_ID('EmpDtls') IS NOT NULL DROP TABLE EmpDtls
IF OBJECT_ID('DeptDtls') IS NOT NULL DROP TABLE DeptDtls

CREATE TABLE #EmpDtls(ID INT, EmpGender CHAR(1))
INSERT INTO #EmpDtls (ID, EmpGender) VALUES (1,'M'),(2,'F'),(3,'M')

SELECT ID, EmpGender FROM #EmpDtls 
-- Update Statement
UPDATE #EmpDtls SET EmpGender = (CASE WHEN EmpGender='M' THEN 'F' WHEN EmpGender='F' THEN 'M' END)

SELECT ID, EmpGender FROM #EmpDtls