CODEWITHSUNDEEP
sqloracle-database
Saurabh
Saurabh

Reputation: 1

How to identify which records were not updated after executing an update query in oracle?

I am trying to update 100 records in oracle through the following update statement, but when I execute it, it says 90 records updated, whereas I have 100 records in the where clause.

Now how to identify which 10 records (codes) are not updated out of 100 in oracle?

In the following statement, I want to know the code names which were not updated? is there any simple trick to know?

update table1 a set a.column1='Yes' where a.column2 in ('code1','code2','code3','code4',........,'code100');

Upvotes: 0

Views: 176

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You can't really directly from the update; but you could use the same list of values (assuming it's a hard-coded list, not coming from a table) as a collection, and then look for values in the collection that are not in the table:

select *
from table(sys.odcivarchar2list('code1','code2','code3','code4',........,'code100')) t
where not exists (
  select null
  from table1 a
  where a.column2 = t.column_value
);

db<>fiddle with a smaller set to demonstrate the idea.

You could modify your update to only update rows which are not already 'Yes'; and if you did that then you could either look for collection values that don't exist at all, or those which exist but don't need to be updated - in that case, before you actually run the update, of course. db<>fiddle.

odcivarchar2list is a built-in collection type, but you could use your own.

If you already have the values in a collection or table you can use that directly, both for this query and for the update.

Upvotes: 2

Related Questions