CODEWITHSUNDEEP
sql
ahmed
ahmed

Reputation: 879

what should be the query for this?

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 ?

Upvotes: 2

Views: 99

Answers (2)

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

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:

Upvotes: 3

Pedro
Pedro

Reputation: 11864

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...

Upvotes: 2

Related Questions