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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
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;

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Alex [***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
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.