
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 ?
Solution: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...
Solution: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:
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon