CODEWITHSUNDEEP
sqloracle-database
user3844609
user3844609

Reputation: 1

Oracle - updating the column in the same table

In oracle, trying to update a column in the same table - like if the column has value 636180 then with 3 as below

update TABLE_A
set wid = (
select distinct case wid
when 636180 then 3
when 636181 then 5
else wid 
end new_wid
from TABLE_A where rownum < 100);

It errors out as

SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"

Please help.

Upvotes: 0

Views: 1158

Answers (1)

juergen d
juergen d

Reputation: 204766

update TABLE_A 
set wid = case when wid = 636180 then 3 
               when wid = 636181 then 5 
               else wid 
          end
where rownum < 100

or to only update the records having the relevant wids

update TABLE_A 
set wid = case when wid = 636180 then 3 
               when wid = 636181 then 5 
          end
where wid in (636180, 636181)

Upvotes: 1

Related Questions