Tutorial :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 ?


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
Previous
Next Post »