CODEWITHSUNDEEP
sqloracle11g
Pramod
Pramod

Reputation: 667

Inner join update

I have following two tables TAB_1

FID   T_NAME_1 NAME2
---------------------

TAB_2

FID   T_NAME_2   NAME3
----------------------

For all matching fid of tab_1 and tab_2 there are some miss- matched fields of T_NAME_1 and T_NAME_2, which should not be there. so I want to update t_name_1 of table tab_1 with all the miss-matched values of t_name_2. I tried following query which returning an error

update tab_1 set t_name_1 = (  select  t2.t_name_2 from tab_2 t2 left join tab_1 t1 on 
t1.fid = t2.fid where t1.t_name_1 <> t2.t_name_2)

Upvotes: 1

Views: 75

Answers (2)

Robert
Robert

Reputation: 25753

Try this solution:

update tab_1 t1 set t_name_1 = (  select  t2.t_name_2 
                                  from tab_2 t2 
                                  where t1.fid = t2.fid 
                                  and t1.t_name_1 <> t2.t_name_2)
where exists (select *
              from tab_2 t2
              where t1.fid = t2.fid
              and t1.t_name_1 <> t2.t_name_2)

Upvotes: 1

Didoo
Didoo

Reputation: 148

Try:

update tab_1 t1
   set t_name_1 = (select t2.t_name_2
                     from tab_2 t2
                    where t1.fid = t2.fid
                      and t1.t_name_1 <> t2.t_name_2)
 where exists (select 1
          from tab_2 t2
         where t1.fid = t2.fid
           and t1.t_name_1 <> t2.t_name_2)

Upvotes: 5

Related Questions