CODEWITHSUNDEEP
sqloracle-databasesql-likesql-deleteora-04091
Sam
Sam

Reputation: 581

Delete rows matching substring with LIKE?

How do you delete rows from a table, where a column contains a substring, but the type of that column is 'Long'. (Yes, I know I shouldn't use Long, but I'm maintaining someone else's mess).

My first attempt was: 

delete from longtable 
  where search_long(rowid) like '%hello%';

(Following on from this answer.)

This returns:

SQL Error: ORA-04091: table blah.longtable is mutating, trigger/function may not see it

Upvotes: 2

Views: 2678

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132750

I just replicated your problem and got the same error - it seems the function can't work from within a DELETE statement. The full text of the error is:

ORA-04091: table HOU.LONGTABLE is mutating, trigger/function may not see it
ORA-06512: at "TONY.SEARCH_LONG", line 4

This procedural approach will work:

begin
  for r in (select id from longtable 
            where search_long(rowid) like '%hello%')
  loop
    delete longtable where id = r.id;
  end loop;
end;

Upvotes: 4

Related Questions