Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Switch the departments of the two employees using a single query

The table name is Employee, here is the table below, I need to switch the departments so that John is purchasing and Jenn is accounting, but in a single query

EmpID EmpName Department
1 John Accounting
2 Jenn Purchasing

I have tried a few things but I can’t seem to find out to do in a single query

Tags:

2

Answers


  1. 0

    You can SELF JOIN employee table, where employee ID is not equal to each other.

    Example Fiddle

    SELECT e1.EmpID, e1.EmpName, e2.Department  
FROM Employee e1
JOIN Employee e2 ON e1.EmpName != e2.EmpName 
ORDER BY  e1.EmpID ;

    Output

    EmpID EmpName Department
    1 John Purchasing
    2 Jenn Accounting
    Login or Signup to reply.
  2. 0

    I need to switch the departments so that John is purchasing and Jenn is accounting, but in a single query

    You know marked data literally, so use simple

    UPDATE Employee
SET Department = CASE EmpName WHEN 'John' THEN 'Purchasing'
                              WHEN 'Jenn' THEN 'Accounting'
                              END
WHERE EmpName IN ('John', 'Jenn');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search