We help IT Professionals succeed at work.

oracle 11g

GouthamAnand
GouthamAnand asked
on
Hi,

I have tables and data like below.

create table test1(country varchar2(30),city varchar2(30),price number);

create table test2(country varchar2(30),city varchar2(30),price number);

insert into test1 values('USA','NY',null);

insert into test1 values('USA','NJ',null);

insert into test1 values('USA','CHI',null);

insert into test2 values('USA','NY',100);

insert into test2 values('USA','NJ',200);

insert into test2 values('USA',null,1000);

commit;

I need to update table test1 as below from the values of table test2

USA      NY      100
USA      NJ      200
USA      CHI      1000

ie if matching city from test1 is there in test2 then apply that price. If matching city not found then apply default price ie where city is null.

Can you please let me know how can I achieve this?


Thanks.
Comment
Watch Question

Try this.
update table1 (price) values 
CASE exists (select price from table2 where table2.country=table1.country and table2.city=table1.city) 
THEN (select price from table2 where table2.country=table1.country and table2.city=table1.city) 
ELSE (select price from table2 where table2.country=table1.country and city is null)
END

Open in new window

declare
cursor c1
is
select * from test2;

type typ is table of c1%rowtype;

vtyp typ;

begin
open c1;
loop
fetch c1 bulk collect into vtyp limit 1000;
FORALL i in 1..vtyp.count
update test1
set price=vtyp(i).price
where country=vtyp(i).country
and city=vtyp(i).city;

exit when c1%notfound;
end loop;
close c1;
end;

Author

Commented:
declare
cursor c1
is
select * from test2 order by city nulls first;

type typ is table of c1%rowtype;

vtyp typ;

begin
open c1;
loop
fetch c1 bulk collect into vtyp limit 1000;
FORALL i in 1..vtyp.count
update test1
set price=vtyp(i).price
where country=vtyp(i).country
and city=vtyp(i).city or vtyp(i).city is null;

commit;
exit when c1%notfound;
end loop;
close c1;
end;
You should be able to achieve this without using PL/SQL. Did you take a look at the MERGE statement?!
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
awking00Information Technology Specialist

Commented:
What would you expect if you had data like this?
Test1
'USA','NY',null
'USA','NJ',null
'USA','CHI',null
'USA','DC',null
Test2
'USA','NY',100
'USA','NJ',200
'USA',NULL,500
'USA',NULL,1000

Author

Commented:
Achieve through a pl/sql block. As I have many columns and implicit update is referring query many times.

Author

Commented:
@awking00,
In our production data we never have two default values.

Thanks.