asked on

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.

Mark Bullock

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

ASKER CERTIFIED SOLUTION

GouthamAnand

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GouthamAnand

ASKER

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;

Alex [***Alex140181***]

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

awking00

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

GouthamAnand

ASKER

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

GouthamAnand

ASKER

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

Thanks.