SQL & PL/SQL

Hint: use a correlated subquery in the set part of the update statement, and then use decode/case within the subquery to decide what the output of the column should be, based on whether the the row exists in table1 or not for the linked id from table2.

Edited by: Boneist on 28-Jul-2010 10:05
Amended hint so it's for update, not select (d'oh!)

See the solution below

SQL> select * from table1;

ID
----------
A123
A456
A678
A890

SQL> select * from table2;

ID         INDI
---------- -----
A678       
A456       
A123       
A666       
A898       
A890       

6 rows selected

SQL> 
SQL> update table2 t2
  2   set INDI =  (
  3                select decode((select 1 from table1 t1 where t1.id = t2.id),1,'MMM','OOO') from dual
  4               );

6 rows updated

SQL> commit;

Commit complete

SQL> select * from table2;

ID         INDI
---------- -----
A678       MMM
A456       MMM
A123       MMM
A666       OOO
A898       OOO
A890       MMM

6 rows selected

Thanks,
Andy

TABLE1
======
ID
-----
A123
A456
A678
A890


TABLE2
======
ID INDI
----- ----
A123
A456
A678
A890
A666
A898


If ID of TABLE2 matches with ID of TABLE1 then update INDI with 'MMM' else update INDI with 'OOO'. How can i achive this?

FInal result will be....

TABLE2
======
ID INDI
----- ----
A123 MMM
A456 MMM
A678 MMM
A890 MMM
A666 OOO
A898 OOO

One more option for the above solution is u can go with NVL2 as welll....

UPDATE TABLE2 T
SET INDI =
NVL2((SELECT 1 FROM TAB1 WHERE TAB1.ID = T.ID),'MMM','OOO')

Hi All, Thanks for your help, problem is solved.

Now i have 10 milion records to be updated. If i ran this stand alon query, it will cause a memory problem.

How can i write PL/SQL block, which can commint after every 10,000-20,000 records?

Try this

declare           
type t_type is table of rowid index by pls_integer;
l_type t_type;
cursor c1 is select rowid from table2;             
begin
open c1;
loop
fetch c1 bulk collect into l_type limit 10000;
exit when c1%notfound;
forall i in l_type.FIRST .. l_type.LAST
update table2 t2
     set INDI =  (
                  select decode((select 1 from table1 t1 where t1.id = t2.id),1,'MMM','OOO') from dual
                 )
     where t2.rowid = l_type(i);
commit;
end loop;
close c1;
end;  

I have not written any exception block but should write one.

PS: I still think you should use the simple update query.
Thanks,
Andy

I like case expression :D

update TABLE2 a
set INDI = case when exists(select 1 from TABLE1 b
                             where b.ID = a.ID)
                then 'MMM' else 'OOO' end;

What do mean by memory problem and how do you know it is going to happen? When you measured the update on a few rows what resources did it consume?

> However you write it it will use more undo, redo and everything else that can be measured and take about twenty times longer.

4339747