1

I have a data of 150 employees and their salaries I need to swap the salary of employee_id 120 and 121 Suppose The salary of employee_id 120 is 8000 and the salary of employee_id 121 is 8200 So I need to swap the salaries of both the id's

Abhiram Varma
  • 105
  • 2
  • 9
  • 3
    Possible duplicate of [SQL UPDATE statement to switch two values in two rows](https://stackoverflow.com/questions/8108798/sql-update-statement-to-switch-two-values-in-two-rows) – ayrusme Oct 02 '17 at 06:31
  • 1
    please mention rdbms ? – Ravi Oct 02 '17 at 06:33
  • If you know the values and the IDs and this is a one off, why don't you just update each employee salary? UPDATE salary_table SET salary = 8000 WHERE empoyee_id=121; and then the same for id 120? – ikkjo Oct 02 '17 at 06:33
  • @thatrockbottomprogrammer - How did you assume that OP is using `SQL SERVER` – Pரதீப் Oct 02 '17 at 06:34
  • The underlying logic of the queries are possibly the same across the databases. Also, Stackoverflow is not a site for assignments or free code. OP has to specify the research and the work they've already done on the same. I'm sure you know this. – ayrusme Oct 02 '17 at 06:36

7 Answers7

1

If you already know the desired final salaries of the two employees, then I think you can just hard code those values in a single UPDATE query:

UPDATE yourTable
SET
    salary = CASE WHEN employee_id = 120 THEN 8000 ELSE 8200 END
WHERE
    employee_id IN (120, 121)

This assumes that you want employee 120 to have a salary of 8000 and 121 to have a salary of 8200, if I read correctly.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

An alternative solution.

;WITH CTE AS (
    SELECT *, 
        RN_ASC = ROW_NUMBER() OVER(ORDER BY employee_id),
        RN_DSC = ROW_NUMBER() OVER(ORDER BY employee_id DESC)
     FROM employees WHERE employee_id IN (120,121)
 )
 UPDATE T1
 SET salary = T2.salary
 FROM CTE T1 INNER JOIN CTE T2 ON T1.RN_ASC = T2.RN_DSC
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44
0

Sample data:-

ID Sal

120 8000

121 2000

Add the salaries of 120 and 121 (Say it is 8000 and 2000) and Update it as the new salary for 120. 120 has the salary as 10000

UPDATE yourtable
   SET sal=(SELECT sum(sal) from yourtable where id in (120,121))
 WHERE ID = 120

ID Sal

120 10000

121 2000

Now update the salary of 121 as the difference between the salary of 121 and 120 

UPDATE yourtable y
   SET sal=(SELECT sal-y.sal from yourtable where id =120)
 WHERE ID = 121

ID Sal

120 10000

121 8000

Now update the salary of 120 as the difference between the salary of 121 and 120

UPDATE yourtable y
   SET sal=(SELECT y.sal-sal from yourtable where id =121)
 WHERE ID = 120

ID Sal

120 2000

121 8000

Valli
  • 1,440
  • 1
  • 8
  • 13
0

With those two id's this simple UPDATE can do it:

update tabename t1
set sal = (select sal from tablename t2 where t2.id = 241 - t1.id)
where t1.id in (120, 121)

For id 120, pick salary from id 241 - 120 => id 121's salary.

For id 121, pick salary from id 241 - 121 => id 120's salary.

jarlh
  • 42,561
  • 8
  • 45
  • 63
0

Also you can try like this, It can be used generically where you need to change only emp_id's if you encounter this kind of issues again.

     declare @str120sal varchar(25);
     declare @str121sal varchar(25);
     select @str120sal=salary from  EmpTbl where employee_id=120;
     select @str121sal=salary from  EmpTbl where employee_id=121;
     update EmpTbl set salary=@str120sal where employee_id=121;
     update EmpTbl set salary=@str121sal where employee_id=120;
Vaibhav More
  • 212
  • 1
  • 14
0

This is the exact answer

update e1  set e1.Salary = (select e2.salary from [EmployeeSalSwap] e2 where e2.id = (155- e1.Id) ) from TempOps.dbo.EmployeeSalSwap as e1    where Id in (75,80)
Kendo
  • 11
  • 4
0
CREATE TABLE  salary (
    emp_id int(11) NOT NULL,
    salary float NOT NULL,
    dept int(11) NOT NULL,
    PRIMARY KEY (emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO salary VALUES(100, 10000, 5),(101, 20000, 4),(103, 15000, 6),(104, 12000, 2)

UPDATE salary s1,salary s2 SET s1.salary = s2.salary, s2.salary = s1.salary WHERE s1.emp_id=100 AND s2.emp_id = 104
Kev
  • 15,899
  • 15
  • 79
  • 112
Nitin Daund
  • 1
  • 1