oracle 11g

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.
GouthamAnandAsked:
Who is Participating?
 
GouthamAnandAuthor Commented:
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;
0
 
Mark BullockQA Engineer IIICommented:
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

0
 
GouthamAnandAuthor 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;
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
0
 
awking00Commented:
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
0
 
GouthamAnandAuthor Commented:
Achieve through a pl/sql block. As I have many columns and implicit update is referring query many times.
0
 
GouthamAnandAuthor Commented:
@awking00,
In our production data we never have two default values.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.