update oracle dara with select statement-CodePudding

I have a table that contains millions of records.I want to update approximately 30.000 rows. I can get data with select * from customer where city is not null. This query returns 30.000 rows.I want to update only that 30000 rows like that

update customer set city = null where (select .....)

I want to update 30.000 rows not all table so I have to use select statement otherwise plain update takes long time :(

Do you have any idea? How can i write a query or script to complete this process.

My trigger is like that,

TRIGGER "CUSTOMER_HIST_TR" AFTER
UPDATE OF city ON Customer  REFERENCING NEW AS NEW OLD AS OLD FOR EACH 
ROW
 DECLARE
  v_osuser varchar2(20);
  v_terminal varchar2(20);
  v_username varchar2(20);
  v_city varchar2(2);
 BEGIN
if nvl(:new.city,'NULL')<>nvl(:old.city,'NULL') then
select osuser,username,terminal
into   v_osuser,v_username,v_terminal
from v$session
where audsid=userenv('SESSIONID');
insert into 
IT_TRG_CITY(customer_id,old,new,osuser,terminal,userid,chng_date) values(:old.customer_id,:old.subs_mask,:new.subs_mask,v_osuser,v_terminal,v_username,sysdate);
end if;
END;

CodePudding user response：

If out of those millions of rows 30.000 of them have something in city column (and you want to have null instead), then

update customer set
  city = null
where city is not null;

Right?

Select you might try instead of yours:

SELECT osuser, username, terminal
  INTO v_osuser, v_username, v_terminal
  FROM (SELECT osuser,
               username,
               terminal,
               ROW_NUMBER () OVER (ORDER BY NULL) rn
          FROM v$session
         WHERE audsid = USERENV ('SESSIONID'))
 WHERE rn = 1;