update values in a table in single query

nainakarri

Hi I have an employee table with column gender F and M. Now i want to update the gender values of all the employee as F with M and M with F. But this should happen in a single query. How can i do this. Thanks for your help. Regards Naina

Naina

Mycroft Holmes

You cannot do it in a single query, you need 3 updates, move F to X, move M to F, move X to M you can wrap the 3 queries in a transaction to insure integrity is maintained.

Never underestimate the power of human stupidity RAH

Lost User

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 :suss: If you can't read my code, try converting it here[^]

gvprabu

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

Lost User

Mycroft Holmes wrote:

You cannot do it

You can actually do it. SQL is a set based language, changes made in a query are either fully committed or fully rolled back, there is no partial update. So, Eddy's solution will actually work.

Mycroft Holmes

Eddie got my 5, I didn't think of using case statement in the update clause.

Never underestimate the power of human stupidity RAH

gvprabu

Hi Naina, Check the Script, U can use CASE Statement in UPDATE.

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

Regards, GVPrabu