Delete on matching multiple values supplied as static list

Question

Expanding on this question - Oracle Delete Rows Matching On Multiple Values, I need to use DELETE ... WHERE ... IN ... from two lists, but I need to do it with text strings that are supplied by an an outside statement, not a SELECT statement.

So for example I have these two strings:

'2810C000000635','2810C000000636','2810C000000637'

28006900,28006901,28006902

and I want to turn them into SQL.

DELETE FROM os_abp_classification WHERE (class_key, uprn) IN ('2810C000000635','2810C000000636','2810C000000637'),(28006900,28006901,28006902)

But running this gives me: SQL Error: ORA-00920: invalid relational operator

This has been asked as Oracle multiple fields in Select IN Parameter - but the given answer doesn't work for me:

DELETE FROM os_abp_classification where (class_key, uprn) IN (
SELECT '2810C000000635','2810C000000636','2810C000000637' from DUAL
    union 
SELECT 28006900,28006901,28006902 from DUAL
)

ORA-00913: too many values

What's the format for such a query? Thanks

EDIT: I'm trying to replicate this functionality, but using the "where ... in" format instead because it's much faster (as discovered in Optimal way to DELETE specified rows from Oracle):

DELETE FROM os_abp_classification WHERE (class_key = '2810C000000635' and uprn = 28006900) or (class_key = '2810C000000636' and uprn = 28006901) or (class_key = '2810C000000637' and uprn = 28006902)

Why not use `WHERE class_key = 1 and uprn = 2 or class_key = 3 and uprn = 4 or ...`? — juergen d, Jul 28 '14 at 09:54
Give a sample! Your error is evident: you're trying to find a match among a 2-ple(class_key, uprn) and a 3-ple(28006900,28006901,28006902 for example), this is a formal error. What you really want to do is not clear! — Alessandro Rossi, Jul 28 '14 at 10:05
@juergend - because I will need to delete thousands of rows, and that method is slow. Using "where in" is much faster. See - http://stackoverflow.com/questions/10092407/optimal-way-to-delete-specified-rows-from-oracle — GIS-Jonathan, Jul 28 '14 at 10:15
I didn't mean SQL samples, those are invalid statements and are meaningless. Give sample tables, sample input and desired result or try to be clear with your request! Your question is uncompromisable at this state. Read it and find if you can understand your problem with it. Printing the slow method to delete rows that works may be enough to clarify what you're trying to do — Alessandro Rossi, Jul 28 '14 at 10:26
Do you need to delete matching pairs from both lists, or any combination of values from both lists, or any rows that matches a value in either list? — Alex Poole, Jul 28 '14 at 10:30
@AlexPoole - Matching Pairs. I've tried to clarify with my edit. — GIS-Jonathan, Jul 28 '14 at 10:31

Alex Poole · Accepted Answer · 2014-07-28T11:25:41.453

If you're deleting matching pairs then the syntax would be:

DELETE FROM os_abp_classification
WHERE (class_key, uprn) IN (
  select '2810C000000635', 28006900 from dual
  union all select '2810C000000636', 28006901 from dual
  union all select '2810C000000637', 28006902 from dual
  union all ...
)

Depending on where your strings are coming from, you could make this more general by splitting those up into separate elements

delete from os_abp_classification
where (class_key, uprn) in (
  with class_keys as (
    select level as rn,
      regexp_substr('2810C000000635,2810C000000636,2810C000000637',
        '[^,]+', 1, level) as class_key
    from dual
    connect by regexp_substr('2810C000000635,2810C000000636,2810C000000637',
      '[^,]+', 1, level) is not null
  ),
  uprns as (
    select level as rn,
      cast(regexp_substr('28006900,28006901,28006902',
        '[^,]+', 1, level) as number) as uprn
    from dual
    connect by regexp_substr('28006900,28006901,28006902',
      '[^,]+', 1, level) is not null
  )
  select ck.class_key, u.uprn
  from class_keys ck
  join uprns u on u.rn = ck.rn
);

Longer with the sample data but if you have a lot of value pairs it might end up shorter, and you might be able to bind the string values depending on where/how you're running this.

The subquery uses a CTE to convert each original string of values into a list of values - left as strings in the first one, cast to numbers in the second. It also assigns a pseudo-row-number to each result in both sets which will be the same for the matching pairs, so then joining the CTEs together gives you the pairs in a form ready for the in clause:

SQL Fiddle showing the results of the CTE join as pairs, the delete based on that, and the rows remaining afterwards.

If you already have lists of values you could also do this:

delete from os_abp_classification
where (class_key, uprn) in (
  with class_keys as (
    select rownum as rn, column_value as class_key
    from table(sys.odcivarchar2list('2810C000000635','2810C000000636','2810C000000637'))
  ),
  uprns as (
    select rownum as rn, column_value as uprn
    from table(sys.odcinumberlist(28006900,28006901,28006902))
  )
  select ck.class_key, u.uprn
  from class_keys ck
  join uprns u on u.rn = ck.rn
);

... but relying on rownum on a query without an explicit order by is risky; it looks OK here but may not be guaranteed to work, and could break in the future even if it does work now. odcivarchar2list is defined as a vararry, which is an ordered set. I think Tom Kyte sort of implies it's OK too. But even so I'd be wary of using this. (Is that enough caveats?)

Thanks for the answer, that does it, though it is very verbose. Note: It does work without the "all" part of the "union all", so a small saving there. Cheers! — GIS-Jonathan, Jul 28 '14 at 10:37
Without the 'all' it suppresses duplicates - which will make no difference to your results, but adds a sort step. Probably not very significant here but I'd use it unless you explicitly do want duplicate suppression, so it's habit apart from anything else. — Alex Poole, Jul 28 '14 at 10:39

Delete on matching multiple values supplied as static list

1 Answers1