Using more than two where clauses in the single update query in SQL Server

Question

I have a table

EmpId   EmpName   ManagerId   Gender                  
1       Shahzad       2         M         
2       Santosh       1         F         
3       Sayanhi       2         M

By mistake 'M' is assigned to female employees and 'F' is assigned to male employees. So I need to write a query to make the correction.

I tried the below query.

UPDATE Employee 
SET Gender='M' 
WHERE EmpId IN (SELECT EmpId FROM Employee WHERE Gender='F') 
  AND Gender='F'
WHERE EmpId IN (SELECT EmpId FROM Employee WHERE Gender='M')

but it didn't work.

Possible duplicate: stackoverflow.com/questions/1136380/…

Erik Ahlswede
– Erik Ahlswede

2016-03-28 18:25:02 +00:00
Commented Mar 28, 2016 at 18:25 — Erik Ahlswede
– Erik Ahlswede, Commented Mar 28, 2016 at 18:25

Tab Alleman · Accepted Answer · 2016-03-28 18:24:37Z

9

here is a simple solution that will fix both cases at once:

UPDATE Employee
SET Gender = CASE Gender
  WHEN 'M' THEN 'F'
  WHEN 'F' THEN 'M'
END

answered Mar 28, 2016 at 18:24

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Igor Over a year ago

Yours is better :)

David T. Macknet · Accepted Answer · 2016-03-28 19:59:11Z

2

Try this instead:

SELECT   empid, empname, CASE WHEN gender = 'M' THEN 'F' ELSE 'M' END AS Gender
INTO     #tmp
FROM     Employee

If you're happy with what you see in there, then:

UPDATE    Employee
SET       Employee.Gender = #tmp.Gender
FROM      Employee
INNER JOIN #tmp
ON        Employee.empid = #tmp.empid

edited Mar 28, 2016 at 19:59

answered Mar 28, 2016 at 18:26

David T. Macknet

3,1803 gold badges29 silver badges36 bronze badges

1 Comment

David T. Macknet Over a year ago

Sorry - qualified Employee.Gender - should work now.

chancrovsky · Accepted Answer · 2016-03-28 19:07:08Z

1

If it's a given that every employee's gender is incorrect, and every gender is already M or F, why not:

UPDATE Employee
   SET Gender=(CASE WHEN Gender='F' 
                    THEN 'M' 
                    ELSE 'F' END)

edited Mar 28, 2016 at 19:07

chancrovsky

5803 silver badges11 bronze badges

answered Mar 28, 2016 at 18:26

David W

10.2k38 silver badges60 bronze badges

Collectives™ on Stack Overflow

Using more than two where clauses in the single update query in SQL Server

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Related