Skip to Main Content
  • Questions
  • Get Primary Keys of Affected Rows in Update Statements

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Neeraj.

Asked: April 26, 2017 - 2:49 pm UTC

Last updated: June 04, 2020 - 12:23 pm UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Question is simple

Is there any way where I can get primary keys of a table which have been updated by update statements ? By doing that I want to leverage a select for an update

Example
There is a table EMPLOYEE with EmployeeID,EmployeeName .I want to update EMPLOYEE table on basis of name and want to get all EmployeeID as a outcome .I know I can do it in normal way where I can select and then update but is there any way that I execute an update and resultant is employeeID


and Chris said...

This is another great reason to use PL/SQL:

You can use the return clause of update to bulk collect all the affected rows into an array!

create table t (
  x int primary key,
  y int
);

insert into t 
  select rownum, mod(rownum, 2) y from dual connect by level <= 10;
commit;

declare
  ids dbms_sql.number_table;
begin
  update t
  set    y = 2
  where  y = 1
  return x 
  bulk collect into ids;
  
  for i in ids.first .. ids.last loop
    dbms_output.put_line('Updated: ' || ids(i));
  end loop;
end;
/

PL/SQL procedure successfully completed.
Updated: 1
Updated: 3
Updated: 5
Updated: 7
Updated: 9

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another help

Neeraj Shukla, April 27, 2017 - 3:45 pm UTC

Thanks for help answer is helpful though I am not looking for PL/SQL block but a simple update statement which can return a value
Connor McDonald
April 27, 2017 - 11:39 pm UTC

That is what has been demonstrated - we simply used PLSQL as a wrapper to define a variable into which we returned the value

A reader, March 27, 2020 - 1:55 pm UTC


Seema, March 27, 2020 - 1:58 pm UTC

I have a question, I have a update statement and which is dynamic sometimes there are scenario when it's not updating a single row also.
In that case this return statement is throwing error:

How can we handle nulls if there is not a single update of rows??

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 20
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
Chris Saxon
March 27, 2020 - 4:43 pm UTC

What exactly is the code you're running? Test case please!

Seema, March 29, 2020 - 3:08 pm UTC

Thanks for coming back!!.
My scenarios is exactly same as above:
declare
ids dbms_sql.number_table;
begin
update t
set y = 2
where y = 100
return x
bulk collect into ids;

for i in ids.first .. ids.last loop
dbms_output.put_line('Updated: ' || ids(i));
end loop;
end;


Only thing is my update statement is trying to update a row Y=100 (which doesnot exist in table) so it will return NULL .

and then the 'for loop' was throwing error, I could fix that by using COUNT.

if ids.count > 0
for i in ids.first .. ids.last loop
dbms_output.put_line('Updated: ' || ids(i));
end loop;
end if;

Thanks for your above post, it really helped me :)
Have a nice day!!

Connor McDonald
March 30, 2020 - 12:20 am UTC

Glad we could help

Seema, March 30, 2020 - 4:08 pm UTC

Same code I am trying in dynamic statement and it is not working while returning the ID.

I am trying something like,
v_sql := 'update MyTable
set <my col updates>
where <my conditions>
||v_cond
|| 'return id bulk collect into MyIDList ';

Kindly help on this.

EXECUTE IMMEDIATE v_sql ;
Connor McDonald
March 31, 2020 - 1:38 am UTC

The id's you are returning are not part of the dynamic part of the SQL, so it would be:

DECLARE
   TYPE NameList IS TABLE OF VARCHAR2(15);
   enames    NameList;
   bonus_amt NUMBER := 50;
   sql_stmt  VARCHAR(200);
BEGIN
   sql_stmt := 'UPDATE employees SET salary = salary + :1 
                RETURNING last_name INTO :2';
   EXECUTE IMMEDIATE sql_stmt
      USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;
/


Seema, March 31, 2020 - 6:43 am UTC

Thank You so much!!!!

I am able to execute the dynamic SQL with the above returning statement.
Thanks a lot again for you quick help.


Chris Saxon
March 31, 2020 - 8:39 am UTC

Great to hear.

Seema, June 03, 2020 - 2:33 pm UTC

Hi Tom,

Again need your help, my code was running with BULK collect into, but sometime it's throwing ORACLE internal error during to BULT returning I guess.
In dynamic SQL whatever rows I m updating, I need 'ID' of that rows. the ID I use to insert in other table.

declare
v_sql_m varchar2(4000);
id_v1 dbms_sql.number_table;
V_COUNT number:=0;
begin

v_sql_m := 'update /*+ parallel(8) index(TEST TEST_INDEX)*/ ABC_OWNER.TEST
set
ID_REQUIREMENT = null,
ASSESS_VALUE = null,
MODIFIED_BY =USER,
MODIFIED_TS =systimestamp,
REASON =''automatic re-assessment''
where studyid = 11111
and CONFIRM_YN =''Y''
and TYPE =''M''
and dml_action = ''NEW VALUE'''
|| ' return id into :list_abc ';

--DBMS_OUTPUT.PUT_LINE(v_sql_m);
EXECUTE IMMEDIATE v_sql_m RETURNING BULK COLLECT INTO id_v1 ;
V_COUNT := sql%ROWCOUNT;

if id_v1.count > 0 then
for i in id_v1.first .. id_v1.last loop
INSERT INTO ABC_OWNER.TEST_COMMENTS (STUDYID,Comm_ID,DESCRIPTION,CREATED_BY,COM_TYPE_ID,MODIFIED_BY)
VALUES (11111,id_v1(i),'automatic background update',USER,20,USER);
END LOOP;
end if;

end;

This code runs fine but sometime I get below error:

Connecting to the database .
ORA-00600: internal error code, arguments: [13030], [20], [], [], [], [], [], [], [], [], [], []
Process exited.
Disconnecting from the database .


Can you please let me know what is the reason and fix for this??
The same rows which are part of above SQL, I could able to update without returning clause.


Chris Saxon
June 04, 2020 - 7:36 am UTC

ORA-600 => Contact support! This is an internal error; you need their help to fix it.

That said, I don't see why you need to use executed immediate here.

You should be able to run the update as static SQL:

update /*+ parallel(8) index(TEST TEST_INDEX)*/ ABC_OWNER.TEST
set ID_REQUIREMENT = null,
ASSESS_VALUE = null,
MODIFIED_BY =USER,
MODIFIED_TS =systimestamp,
REASON ='automatic re-assessment'
where studyid = 11111
and CONFIRM_YN ='Y'
and TYPE ='M'
and dml_action = 'NEW VALUE'
RETURNING id BULK COLLECT INTO id_v1 ;


Which may resolve the issue.

seema, June 04, 2020 - 10:59 am UTC

Hi,
As you said, I have written a simple update instead of dynamic and this resolved my issue.

But in my package I have this this kind of dynamic SQL two more times, and the condition in the where clause is not direct like the given one, it's dynamically built. so I cant convert that in simple update. I need the ID of the update so doing bulk return too. Currently other two are running fine. but in future if those two errors out then I will have problem.

Shall I contact support for this ORA-00600??
Chris Saxon
June 04, 2020 - 12:23 pm UTC

If you have ORA-600s, (and no obvious ways to solve it) you need to work with support to resolve it.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library