what should be the query for this ?

Question

how do I swap records in SQL . I have a case in which I have to swap records of one employee to other , for example I have to update like " A's hat on B's head and B's hat on A's head" . Only I need the query , i have the fields.

what should be the best approach for this? Is there any easy query for this ?

Answer 1

declare @hatIdA int
declare @hatIdB int

select @hatIdA = hatId from employees where empID = 'A'

select @hatIdB = hatId from employees where empID = 'B'

update employees set hadId = @hatIdB where empID = 'A'

update employees set hadId = @hatIdA where empID = 'B'

I'd do this way...

Answer 2

Example with hats and employees 'A' and 'B', using a self-join on the employees table for MySQL:

UPDATE
    employees AS e1
JOIN 
    employees AS e2 ON (e1.employee_id = 'A' AND e2.employee_id = 'B')
SET
    e1.hat = e2.hat,
    e2.hat = e1.hat;

Further reading:

• Microshell - Swap values in 2 rows SQL