I have Table 1 : EMP as below
ID NAME CITY AMT ------------------------------------------- 1 sajani Bangalore 0 2 Prashanth Bangalore 0 3 Jayvin Bangalore 0
Table 2: EMP1
ID NAME1 CITY1 AMT1 --------------------------------------------- 1 Sajani Bangalore 10 1 Sajani Bangalore 10 2 Prashanth Bangalore 10 3 Jayvin Bangalore 10
ID is the Key and is common in both the files. I want an Update SQL to update the amount field of Table 1 using Amount field of table 2, which gives the result as below.
Result:
ID NAME CITY AMT -------------------------------------------- 1 sajani Bangalore 20 2 Prashanth Bangalore 10 3 Jayvin Bangalore 10
Advertisement
Answer
One method is a correlated subquery:
update emp set amt = (select sum(e1.amt) from emp1 e1 where e1.id = emp.id);
You can check if the values match using:
update emp set amt = (select sum(e1.amt) from emp1 e1 where e1.id = emp.id); where amt <> (select sum(e1.amt) from emp1 e1 where e1.id = emp.id);