Skip to content
Advertisement

Using subquery with update SQL

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);
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement