CODEWITHSUNDEEP
sqloracle-database
chetan
chetan

Reputation: 3255

case when exists in oracle update query

I have update query like

update dedupctntest a set a.city = case when exists(
select b.shortname from DEDUPADDRESSDICT where lower(a.city) =lower(b.shortname) and rownum = 1) b  then
b.fullname else a.city end;

but it will give missing keyword error

can anybody tell what is wrong in that ?

Upvotes: 1

Views: 3245

Answers (2)

Pavan
Pavan

Reputation: 4339

Try something like below

update dedupctntest a set a.city = case when exists( 
select * from DEDUPADDRESSDICT where lower(a.city) =lower(b.shortname) and rownum = 1) b  then 
(select fullname form DEDUPADDRESSDICT where lower(a.city) =lower(b.shortname) and rownum = 1)
b.fullname else a.city end;

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132640

You cannot reference b.fullname outside its scope, which is inside the exists() clause.

Maybe this does what you need:

update dedupctntest a
set a.city = coalesce
              ( ( select b.fullname 
                  from DEDUPADDRESSDICT 
                  where lower(a.city) = lower(b.shortname) 
                  and rownum = 1
                )
              , a.city
              );

i.e. if the query from DEDUPADDRESSDICT returns a non-null fullname use that, else use a.city. Note that if there is a row in DEDUPADDRESSDICT with a null fullname then a.city will be used.

Upvotes: 5

Related Questions